VB error message - "Missing operator in Query operation

[cassidybklyn]

Please help:
I am trying to populate a recordset using a SELECT query and I am getting an error that says: "Missing operator in query operation".
See query belowI am using VB 6.0).

Set rsin = dbs.OpenRecordset("SELECT tblUnclaimed.PassNumber,tblUnclaimed.EmployeeName" _
& "tblUnclaimed.Original_Check_Number,tblUnclaimed.Original_Check_Date" _
& "tblUnclaimed.Amount_of_Check,EmployeeInfo.Status,tblL3Desc.L3,tblL3Desc.L3_Desc" _
& "FROM(EmployeeInfo Right JOIN tblUnclaimed ON(EmployeeInfo.L1 = tblUnclaimed.L1)" _
& "AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber)) LEFT JOIN tblL3Desc" _
& "ON(EmployeeInfo.L1 = tblL3Desc.L1) AND (EmployeeInfo.L3 = tblL3Desc.L3)" _
& "AND (EmployeeInfo.L5 = tblL3Desc.L5)" _
& "WHERE (((tblUnclaimed.Original_Check_Date) < #2/8/2007#)" _
& "AND ((tblUnclaimed.Status) = 'U';")
Thank you all.
C.

 

[gmmastros]

Check your where clause. You appear to have 5 open parenthesis, but only 3 close parenthesis.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ddiamond]

Also be careful about your line continuations. Looks like you are missing some commas and spaces. Just to name 2 examples (there are others), you need a comma after [blue]tblUnclaimed.EmployeeName[/blue] and a space after [blue]LEFT JOIN tblL3Desc[/blue].

 

[Andrzejek]

Save yourself a lot of problems by:

[blue]Dim strSQL As String[/blue]

strSQL = "SELECT tblUnclaimed.PassNumber,tblUnclaimed.EmployeeName" _
& "tblUnclaimed.Original_Check_Number,tblUnclaimed.Original_Check_Date" _
& "tblUnclaimed.Amount_of_Check,EmployeeInfo.Status,tblL3Desc.L3,tblL3Desc.L3_Desc" _
& "FROM(EmployeeInfo Right JOIN tblUnclaimed ON(EmployeeInfo.L1 = tblUnclaimed.L1)" _
& "AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber)) LEFT JOIN tblL3Desc" _
& "ON(EmployeeInfo.L1 = tblL3Desc.L1) AND (EmployeeInfo.L3  = tblL3Desc.L3)" _
& "AND (EmployeeInfo.L5 = tblL3Desc.L5)" _
& "WHERE (((tblUnclaimed.Original_Check_Date) < #2/8/2007#)" _
& "AND ((tblUnclaimed.Status) = 'U';")

[blue]Debug.Print strSQL[/blue]
[green]'See your SQL in Immiediate Window[/green]
[blue]Set rsin = dbs.OpenRecordset(strSQL)[/blue]

Have fun.

---- Andy

 

[johnwm]

First your line continuation will work better as

strSQL = "SELECT tblUnclaimed.PassNumber,tblUnclaimed.EmployeeName" & _

and so on.

I usually concatenate my querystring into a variable, then use the variable. That way you can stick in a debug statement before using the string and catch most of those little hiccups.

strSQL = "SELECT tblUnclaimed.PassNumber,tblUnclaimed.EmployeeName" & _
 "tblUnclaimed.Original_Check_Number,tblUnclaimed.Original_Check_Date" & _
 "tblUnclaimed.Amount_of_Check,EmployeeInfo.Status,tblL3Desc.L3,tblL3Desc.L3_Desc" & _
 "FROM(EmployeeInfo Right JOIN tblUnclaimed ON(EmployeeInfo.L1 = tblUnclaimed.L1)" & _
 "AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber)) LEFT JOIN tblL3Desc" & _
 "ON(EmployeeInfo.L1 = tblL3Desc.L1) AND (EmployeeInfo.L3  = tblL3Desc.L3)" & _
 "AND (EmployeeInfo.L5 = tblL3Desc.L5)" & _
 "WHERE (((tblUnclaimed.Original_Check_Date) < #2/8/2007#)" & _
 "AND ((tblUnclaimed.Status) = 'U';"
 Debug.Print strSQL
Set rsin = dbs.OpenRecordset(strSQL)

You will then easily spot your missing and faulty bits

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints