Many thanks for your quick reply Kevin. That has definitely fixed variable
sInvoice. It is again erroring with the same error message, but this time,
with variable sInvoiceType. This is supposed to be a character, e.g. Q. I am
leaving it as a string but obviously this isn't correct. Can you kindly shed
some light into this?

Regards,


Dim sInvoice As String
Dim sInvoiceType As String

sInvoice = Worksheets("Sheet1").Range("B1").Value
sInvoiceType = Worksheets("Sheet1").Range("B2").Value

Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Test;",
Destination _
:=Range("A1"))
.CommandText = Array( _
"SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID,
Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber,
Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1,
Sales.DeliveryAddressL" _
, _
"ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines
ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE
ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " & sInvoice &
") AND (Sales.InvoiceStatusID = sInvoiceType ))" _
)
.Name = "Quote header"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Range("B20").Select

End Sub


--
MS SBS 2003 SP2


"Kevin Beckham" wrote:

should read..

..
& "WHERE ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= " &
sInvoice & ") AND (Sales.InvoiceStatusID='Q'))" )

sInvoice was left as text in your query, not converted to a value


"Mentos" wrote:

hi,

the following is a macro i recorded. it's querying an myob database. it
works fine if i don't use variable sInvoice in the where clause. i.e. if i
replace with value '1234'. In its current state i get the run time error at

.Refresh BackgroundQuery:=False

Any help would be appreciated!!

Thanks,


Dim sInvoice As String
Dim sInvoiceType As String

sInvoice = InputBox("MYOB Invoice Number:", "Enter Invoice Number")
sInvoice = "'" & sInvoice & "'"

Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN= Test;",
Destination _
:=Range("A1"))
.CommandText = Array( _
"SELECT Sales.InvoiceNumber, Sales.InvoiceStatusID,
Sales.SalesPersonID, Sales.DeliveryAddress, Sales.CustomerPONumber,
Sales.Memo, Sales.InvoiceDate, Sales.DeliveryAddressLine1,
Sales.DeliveryAddressL" _
, _
"ine2" & Chr(13) & "" & Chr(10) & "FROM ItemSaleLines
ItemSaleLines, Sales Sales" & Chr(13) & "" & Chr(10) & "WHERE
ItemSaleLines.SaleID = Sales.SaleID AND ((Sales.InvoiceNumber= sInvoice ) AND
(Sales.InvoiceStatusID='Q'))" _
)
.Name = "Quote header"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Range("B20").Select

End Sub