Trying to use integer variable in SQL query

[Minkus]

Dim POFROM As Integer

POFROM = Range("H1&quot.Value
Range("B1&quot.Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=Vantage 5 OID;UID=;PWD=;DB=vantage;OIDP=TCP;OIDS=odbc50;OIDH=vmslinux;DBAM=Direct;DBPR=TCP;DBPA=e:\epic50\vantage\db\;ASC=0" _
), Array(";SR=1;GST=0&quot)
' .CommandText = Array( _
'"SELECT POHeader.PONum, POHeader.OrderDate, PORel.DueDate" & Chr(13) & "" & Chr(10) & "FROM vantage.POHeader POHeader, vantage.PORel PORel" & Chr(13) & "" & Chr(10) & "WHERE POHeader.PONum = PORel.PONum AND POHeader.Company = PORel.Company AND ((POHeader.PO" _
' , "Num>POFROM))" & Chr(13) & "" & Chr(10) & "ORDER BY POHeader.PONum&quot
.CommandText = Array( _
"SELECT POHeader.PONum, POHeader.OrderDate, PORel.DueDate, POHeader.EntryPerson " & Chr(13) & "" & Chr(10) & "FROM vantage.POHeader POHeader, vantage.PORel PORel" & Chr(13) & "" & Chr(10) & "WHERE POHeader.PONum = PORel.PONum AND POHeader.Company = PORel.Company AND ((POHeader.PO" _
, "Num>=0 )) AND (POHeader.OrderDate > PORel.DueDate)" & Chr(13) & "" & Chr(10) & "ORDER BY POHeader.PONum&quot
.Refresh BackgroundQuery:=False
End With
End Sub

When I have a value in instead of POFROM the query works perfect but when I try to replace with the variable POFROM it doesn't. In visual basic when I highlight or have a watch on POFROM it says it contains the value of cell H1 but the query doesn't execute, Runtime error 1004 General ODBC error. I have some working queries that use date and string variables in the query so I am hoping that it is just a matter of enclosing the variable name with the correct parentheses, examples below for date and string. Any ideas on what needs to be done here, I created the macro but just running a query whilst recording and then just doing some tinkering afterwards.

date is (RcvDtl.ReceiptDate>={d '" & start_date$ & "'})
string is (Vendor.VendorID>='" & from_id$ & "')

 

[xlbo]

change:
AND ((POHeader.PO" _
' , "Num>POFROM))" to

AND ((POHeader.PONum>" & POFROM & &quot)"

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244