Syntax Error in SQL statement

[CCIDBMNG]

Without getting into full details can anyone by looking at the following sql statement tell me where the syntax error is...

a$ = "SELECT [First Choice].ID, [Res Rep Volume].SSN, [Res Rep Volume].Net, [First Choice].Type, [First Choice].[Product Code] FROM [First Choice] RIGHT JOIN [Res Rep Volume] ON [First Choice].[Rep SSN] = [Res Rep Volume].SSN" & _
"WHERE ((([First Choice].Type)='Sale' Or ([First Choice].Type)='resubmit') AND (([First Choice].[Product Code])='RFC') AND (([First Choice].[Order Date])<=" & [Forms]![EnterPayDate]![txtWritten] & ") AND (([First Choice].[Status 1]) Like" & _
[Forms]![EnterPayDate]![txtOddDate] & ") AND (([First Choice].[Received Date])<=" & [Forms]![EnterPayDate]![txtReceived] & ")) OR ((([First Choice].Type)='Sale' Or ([First Choice].Type)='resubmit') AND (([First Choice].[Product Code])='RFC')" & _
"AND (([First Choice].[Order Date])<=" & [Forms]![EnterPayDate]![txtWritten] & ") AND (([First Choice].[Received Date])<=" & [Forms]![EnterPayDate]![txtReceived] & ") AND (([First Choice].[Status 2]) Like " & [Forms]![EnterPayDate]![txtOddDate] & "))" & _
"OR ((([First Choice].Type)='Sale' Or ([First Choice].Type)='resubmit') AND (([First Choice].[Product Code])='RFC') AND (([First Choice].[Order Date])<=" & [Forms]![EnterPayDate]![txtWritten] & ") AND (([First Choice].[Received Date])<=" & _
[Forms]![EnterPayDate]![txtReceived] & ") AND (([First Choice].[Status 3]) Like " & [Forms]![EnterPayDate]![txtOddDate] & "));"

Any help would be greatly appreciated. I am not very good at SQL statements. I copied this from the SQL view of my query and added the quotes where I thought they were needed for it to run in my code.

Thanks in advance.

 

[Mile-O]

a$ = "SELECT [First Choice].ID, [Res Rep Volume].SSN, [Res Rep Volume].Net, [First Choice].Type, [First Choice].[Product Code] FROM [First Choice] RIGHT JOIN [Res Rep Volume] ON [First Choice].[Rep SSN] = [Res Rep Volume].SSN" & _
"WHERE ((([First Choice].Type)='Sale' Or ([First Choice].Type)='resubmit') AND (([First Choice].[Product Code])='RFC') AND (([First Choice].[Order Date])<=" & [Forms]![EnterPayDate]![txtWritten] & ") AND (([First Choice].[Status 1]) Like" & _
[Forms]![EnterPayDate]![txtOddDate] & ") AND (([First Choice].[Received Date])<=" & [Forms]![EnterPayDate]![txtReceived] & ")) OR ((([First Choice].Type)='Sale' Or ([First Choice].Type)='resubmit') AND (([First Choice].[Product Code])='RFC')" & _
"AND (([First Choice].[Order Date])<=" & [Forms]![EnterPayDate]![txtWritten] & ") AND (([First Choice].[Received Date])<=" & [Forms]![EnterPayDate]![txtReceived] & ") AND (([First Choice].[Status 2]) Like " & [Forms]![EnterPayDate]![txtOddDate] & "))" & _
"OR ((([First Choice].Type)='Sale' Or ([First Choice].Type)='resubmit') AND (([First Choice].[Product Code])='RFC') AND (([First Choice].[Order Date])<=" & [Forms]![EnterPayDate]![txtWritten] & ") AND (([First Choice].[Received Date])<=" & _
[Forms]![EnterPayDate]![txtReceived] & ") AND (([First Choice].[Status 3]) Like " & [Forms]![EnterPayDate]![txtOddDate] & "));"

I'm not too hot in JOIN style SQL but looking I see this one thing:

[Res Rep Volume].SSN" & _
"WHERE ((([First Choice].Type)='Sale'

Where it will read as [Res Rep Volume].SSNWHERE ((([First Choice].Type

The same with all the rest; need to get a space in each.

 

[CCIDBMNG]

Thank you so much I didn't notice that but it's still giving me a syntax error. I've looked at this time and time again I don't know what's wrong with it. Someone please help.

 

[CCIDBMNG]

I didn't realize before but the error says "Missing Operator in query expression" if that helps anyone

 

[Mile-O]

I think you are missing a closing bracket somewhere - don't know where - but I counted one more open bracket than I did closed brackets, even then I may have got lost in the counting.

 

[CCIDBMNG]

I counted the brackets and they all seem to be there. Thanks again for trying.

 

[Mile-O]

What about hash marks on either side of your date fields i.e. [Your Field] = #" & txtYourDateControl & "# AND " etc.?

 

[CCIDBMNG]

You were right again but that still doesn't solve the syntax problem. But again thank you soooo much for trying to help me I really appreciate it.

 

[CCIDBMNG]

You were right again but that still doesn't solve the syntax problem. But again thank you soooo much for trying to help me I really appreciate it.

 

[simongallop]

Try changing the splits of the 2nd/3rd lines and the 5th/6th lines so that you are doing the ..." & _ "... within the string rather than kicking straight into a variable on the new line.

 

[CCIDBMNG]

I tried your suggestion and it didn't change anything. Thanks for trying though.

Maybe this will help...

I am assigning the SQL statement to a variable and I don't receive the error until I do the statement Set RS = currentdb.openrecordset(a$). But it says there is a syntax error somewhere in a$ that I am missing an operator.

Here is what a$ equals when I receive the error...

SELECT [First Choice].ID, [Res Rep Volume].SSN, [Res Rep Volume].Net, [First Choice].Type, [First Choice].[Product Code] FROM [First Choice] RIGHT JOIN [Res Rep Volume] ON [First Choice].[Rep SSN] = [Res Rep Volume].SSN WHERE ((([First Choice].Type)="Sale" Or ([First Choice].Type)="resubmit") AND (([First Choice].[Product Code])="RFC") AND (([First Choice].[Order Date])<= #2/9/2003#) AND (([First Choice].[Status 1]) Like *02/26/2003*) AND (([First Choice].[Received Date])<= #2/11/2003#)) OR ((([First Choice].Type)="Sale" Or ([First Choice].Type)="resubmit") AND (([First Choice].[Product Code])="RFC") AND (([First Choice].[Order Date])<= #2/9/2003#) AND (([First Choice].[Received Date])<= #2/11/2003#) AND (([First Choice].[Status 2]) Like *02/26/2003*)) OR ((([First Choice].Type)="Sale" Or ([First Choice].Type)="resubmit") AND (([First Choice].[Product Code])="RFC") AND (([First Choice].[Order Date])<= #2/9/2003#) AND (([First Choice].[Received Date]) <= # 2/11/2003#) AND (([First Choice].[Status 3]) Like *02/26/2003*)) WITH OWNERACCESS OPTION;

I'm hoping that by looking at this someone will be able to figure out what I'm missing. Thanks in advance.

 

[Mile-O]

Looking at that new post brings more questions:

i) Like *02/26/2003* - if the date is being treated as a string put the quotation marks around it, if it's a date put the hash marks around it

ii) Can you really say that something is like a date when a date is basically just a one off value?

 

[CCIDBMNG]

I say LIKE because the field is a text field and would read something like "Accepted 02/26/03" so I am trying to find all the records that have the date 02/26/03 in it. When I use an actual query this works perfectly. It's when I copied the SQL statement to code to do it that way that I have the problem.

 

[FoFa]

(([First Choice].[Status 1]) Like *02/26/2003*)

What is the datatype of Status 1? What ever it is this will not work. It must be a string, and in that case you need to enclose your target in quotes.

Like "*02/26/2003*"

If it is a date field, than you should not be using like.

Of course since you are assigning this to a variable, sub single quote for double.

 

[CCIDBMNG]

The field is text. I placed single quotes and I tried double when I do this now I get the error of "Too Few Parameters. Expected 1"
on the code SET RS = CURRENTDB.OPENRECORDSET(A$).

 

[simongallop]

I think that set rs as recordset indicates that you are using an existing query.

Try creating a query and then setting the SQL value to your string

ie:

Dim MyQD as QueryDef
Set MyQD = Currentdb.CreateQueryDef("TestQuery",A$)

HTH

 

[CCIDBMNG]

That works but I'm not familiar with querydef I need to be able to loop through this query and update another table based on the id number from each record. How can I do that with querydef?

 

[simongallop]

After:

Dim MyQD as QueryDef
Set MyQD = Currentdb.CreateQueryDef("TestQuery",A$)

Add:

Set MyRS = currentdb.openrecordset("TestQuery") {Or whatever name you have used in CreateQueryDef}

and carry on as normal

HTH

 

[CCIDBMNG]

Ok I did that and on the

set rs = currentdb.openrecordset("TestQuery")

I receive the Too Few Parameters. Expected 1. Error again.

 

[simongallop]

What version of access are you using? Also try MyQD.close just before set MyRS etc.