Thank you so much, it now works!

Regards,


--
MS SBS 2003 SP2


" wrote:

On Jul 5, 2:32 am, Mentos wrote:
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- Hide quoted text -

- Show quoted text -

") AND (Sales.InvoiceStatusID = sInvoiceType ))" _

Because you have sInvoiceType inside the quotes, it is not a variable
but a litteral string.

In the original example, the field InvoiceStatusID is a string so I am
assuming that sInvoiceType is a string variable holding the letter Q
(or something)

So that line should be written as:
") AND (Sales.InvoiceStatusID = '" & sInvoiceType & "' ))" _

Note that there are single quotes inside the double quotes so that the
result has quotes around the contents of the variable sInvoiceType and
results in something like:
Sales.InvoiceStatusID = 'Q'

Peter