I think this line is your problem

Set cellPointer = Worksheets("Sheet1").Range(BILL & looper)

You can't pass a Range value to the SQL. You can only pass the value in the
rnage

try this

cellPointer = Worksheets("Sheet1").Range(BILL & looper).Value

If the Rage is more than one cell you will still have a problem because
excel will make cellpointer an array.

I always get my Queries working by first manualy doing the query from the
worksheet by doing the following

Data - Import External Data - New Database Query. Use a fix value for the
cellpointer value. Get this working. Then modify the query to use a Range
location from the worksheet. If you still get errors post the recorded query
and I will make the necessary changes.

"mju" wrote:

I keep getting Syntax error (missing operator) in query
I have tried all ican change the statement but it is still not working
Sub DATABASE()

Const BILL = "a"
Const DOC = "b"
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim looper As Long
Dim BILLNO As String, DOCNO
Dim cellPointer As Variant


strConn = "C:\Documents and Settings\Desktop\smart.mdb"
Set con = New ADODB.Connection
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strConn
laname = InputBox("Enter SENDING Traping Partner")
Cells(1, 1).Value = laname
For looper = 1 To Range(BILL & Rows.Count).End(xlUp).Row
Set cellPointer = Worksheets("Sheet1").Range(BILL & looper)
sSQL = "SELECT tblTradePartner.* FROM tblTradePartner WHERE
tblTradePartner.BILL NO = " & cellPointer & " "

Set rs = New ADODB.Recordset
rs.Open sSQL, con, adOpenStatic, adLockOptimistic
If Not IsNull(rs.Fields("DOC NO").Value) Then
Range(DOC & looper) = rs.Fields("DOC NO").Value
End If
rs.Close
Set rs = Nothing
Next looper

cnn.Close
Set cnn = Nothing
End Sub