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