Missing Operator

[benniesanders]

Hi there,

I am getting a missing operator error on the following (this is how the error message reads):

'(([RECEIVED] between #01/01/2002# and #03/01/2002#)ORDER BY [PLANID]'

I am doing a report from a form and the user chooses the date then the sort order. When I comment out the order by code, it works fine so there's something missing there and I can't find it. Any help would be appreciated. Thanks in advance.

Code from form:

strdate = "[RECEIVED] Between #" & Format(BegDate, "mm/dd/yyyy&quot & "# And #" & Format(EndDate, "mm/dd/yyyy&quot & "#"

If Me.frameOrder = 1 Then
strOrderby = " ORDER BY [PLANID];"
end if

strAll = strdate & strOrderby

DoCmd.OpenReport ("rptJobReport&quot, View:=acViewPreview, Wherecondition:=strAll

 

[scriverb]

The problem with the following is:

'(([RECEIVED] between #01/01/2002# and #03/01/2002#)ORDER BY [PLANID]'

There needs to be a space between the end of the Where statement and the beginning of the Order by. So it should look like thisnew space is in the red area)

'(([RECEIVED] between #01/01/2002# and #03/01/2002#) ORDER BY [PLANID]'

The code that you posted looks okay because it has a space before the word Order. Make sure when you posted your code you didn't fix it yourself.

If Me.frameOrder = 1 Then
strOrderby = " ORDER BY [PLANID];"
end if

If need be put an extra space in there. It won't hurt a thing. But, that is where you problem lies.

Bob Scriver

 

[benniesanders]

No luck, Bob. but thanks for the answer. I have a space and when I run the query as a stand alone it works fine. It just doesn't work from the form to run the report. I don't know why. Thanks again.

 

[scriverb]

Okay I see the problem. When you are running the report you are using the StrAll as the Wherecondition.

strAll = strdate & strOrderby

DoCmd.OpenReport ("rptJobReport&quot, View:=acViewPreview, Wherecondition:=strAll

The Wherecondition should have in it ONLY the code to retrieve the records NOT the sort order information.

Wherecondition Definition: A string expression that's a valid SQL WHERE clause without the word WHERE.

WHERE clause Definition: The part of a SQL statement that specifies records to retrieve.

Leave the OrderBy string off and it works, correct? You will have to update the Sort order by other means. I can help you with that through a query.SQL property update if you would like. Just let me know.

Bob Scriver

 

[benniesanders]

No luck again. I think it just won't because the where condition is just that, it's a "where" condition, not an order by clause. I have to think of something else.

Thanks anyway!

 

[benniesanders]

Well, Bob, after going crazy for hours, I checked the filter box in the report itself and it had an old query in it. I have actually found this to happen when there is an error in a query that opens a report run from a form. The old query stays in the filter of the report and has to be manually deleted. I should have checked it before spending so much time on this stupid report query. Thanks so much for your help and I'm sorry I bothered you with such a "non problem".

 

[scriverb]

No Problem. Glad you were able to figure it out.

Bob Scriver