vb6 - Rtime Error 3075: Syntax error - Missing Operator in query expression

[GiftX]

Gentlemen,
they say more eyes are better than two. Can anyone see if they can see where my syntax error is in the code below:

Set rsin = Dbs.OpenRecordset("SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.Original_Check_Date, tblUnclaimed.Amount_of_Check, EmployeeInfo.Status " _
& "FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) " _
& "WHERE (((EmployeeInfo.Status) Like 'N*') AND ((EmployeeInfo.Union_Code) In (Unioncode) AND ((tblUnclaimed.Status) In ('R','P','N'))) OR (((EmployeeInfo.Union_Code) In (Unioncode) AND ((tblUnclaimed.Status) In ('R','P','N')) AND ((Len([EmployeeInfo].[Status]))=1)) " _
& "AND tblUnclaimed.ReIssued_Check_Date Between #Begindate# And #Enddate# " _
& "ORDER BY EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.PassNumber, tblUnclaimed.Original_Check_Date;")

Any quick response will be appreciated.
Giftx.

[Hack]

When I break your query up into something a bit more easily readible, I find that the end parenthese on the ORDER BY clause throws me an error.

Dim sSQL As String
sSQL = "SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, "
sSQL = sSQL & "EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, "
sSQL = sSQL & "EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, "
sSQL = sSQL & "tblUnclaimed.Original_Check_Date, "
sSQL = sSQL & "tblUnclaimed.Amount_of_Check, EmployeeInfo.Status "
sSQL = sSQL & "FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = blUnclaimed.L1) "
sSQL = sSQL & "AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) "
sSQL = sSQL & "WHERE (((EmployeeInfo.Status) Like 'N*') "
sSQL = sSQL & "AND ((EmployeeInfo.Union_Code) In (Unioncode) "
sSQL = sSQL & "AND ((tblUnclaimed.Status) In ('R','P','N'))) "
sSQL = sSQL & "OR (((EmployeeInfo.Union_Code) In (Unioncode) "
sSQL = sSQL & "AND ((tblUnclaimed.Status) In ('R','P','N')) "
sSQL = sSQL & "AND ((Len([EmployeeInfo].[Status]))=1)) "
sSQL = sSQL & "AND tblUnclaimed.ReIssued_Check_Date Between #Begindat# And #Enddate# "
sSQL = sSQL & "ORDER BY EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.PassNumber, tblUnclaimed.Original_Check_Date;"

[GiftX]

Good morning Hack.
Thanks for the sql cleanup. I ran the sql and got:
syntax error in date in query expression on

strSQL = strSQL & "AND tblUnclaimed.ReIssued_Check_Date 
Between # Begindate# And #Enddate#

Then I changed the date strings to be dynamic as so:

strSQL = strSQL & "AND tblUnclaimed.ReIssued_Check_Date 
Between #" & Begindate & "# And #" & Enddate & "# "

Then I got another error: 3061 Too few parameters. Expected 2.

could you check for my syntax error? I'm not too good with placing quotes.
Thanks.
Giftx.

[Hack]

Add "Debug.Print strSQL" after the last line and before you execute it.

What is goes into your Immediate Window?

[GiftX]

Hack,
I noticed something when I did

Debug.Print strSQL

It picked user user-supplied input: for Begindate and enddate but did not pick up user-supplied input for Unioncode and CheckStatus.

Below is the output of the "Debug.Print"

SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.Original_Check_Date, tblUnclaimed.Amount_of_Check, EmployeeInfo.Status FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) WHERE (((EmployeeInfo.Status) Like 'N*') AND ((EmployeeInfo.Union_Code) In (Unioncode) AND ((tblUnclaimed.Status) In (CheckStatus))) AND ((Len([EmployeeInfo].[Status]))=1)) AND tblUnclaimed.ReIssued_Check_Date Between #03/1/2008# And #03/31/2008# ORDER BY tblUnclaimed.PassNumber;

Giftx.

[Hack]

It picked user user-supplied input: for Begindate and enddate but did not pick up user-supplied input for Unioncode and CheckStatus.

I knew it had to be something like that, and, therein, lies your snytax error.

[GiftX]

So, how do I fix it? I changed this to add quotes to Checkstatus and I had the same error. This time it is not even picking up the variable name, rather it has empty parenthesis ().

Giftx.

[GiftX]

So, how do I fix it? I changed this to add quotes to Checkstatus and I had the same error. This time it is not even picking up the variable name, rather it has empty parenthesis ().

strSQL = strSQL & "AND ((tblUnclaimed.Status) In (" & CheckStatus & "))) "

Giftx.

[Hack]

What is CheckStatus supposed to be?

[GiftX]

Resolved!!!

Thanks To All You Guys For Your Help.

Giftx.

[Hack]

How did you resolve it?

Your solution might help others.

[GiftX]

Techinically it was not resolved.
Some of the good people suggested taking a more user-friendly approach to avoid users making mistakes by entering invalid data without proper string formating.

So I went with using controls so that they can simply select values from listbox and comboboxes.

[Hack]

If it isn't resolved, then what are the lingering questions?