Runtime 3077 (Access 2K sp3)

Topic

New Reply

Hello all: I am getting a ‘3077 Syntax error in string in expression’ using the code below. This is in a report and it happens about 1/3 of the way into the report, which means about 300 or so records pass through this. Any suggestions?? Thanks in advance.

rst.FindFirst “[rptMonth] = #” _
& RepDate & “# and [ProductName] = ‘” _
& Me![ProductName] & “‘”

Reply | Quote

Replies

Have you checked the fields in the table for NULL values. Also look for any values in the referenced fields that “look” strange.
The error is basiclly saying that something you are trying to use in the string you are building is goofy. With dates it could be
a value that does not equate to a valid date(ex 2/30/2005 or Frank).

Hope this helps.

Reply | Quote

Thanks for the reply, Richard. I could not find anything wrong with the data, I’m attaching a scaled-down version. Thanks again

Reply | Quote

What are you trying to get with this report?

Do you want the month in the headings or the month and year?

Why don’t you create a crosstab query for the report?

Reply | Quote

Thanks for the reply Patrick:

I’m getting the month and year in the report. This is a rolling 12 month report so that when a new month is added the oldest drops off. I don’t know if I can accomplish that with a crosstab query, but I may have to try if this problem isn’t solved. Thanks again.

Reply | Quote

Why don’t you specify start and end dates in a form then call the report from there, you could then use the crosstab query idea then.

Reply | Quote

On looking at your data there is a name called {Cable Sling 3/8″ x 4′}, this has got both single and double quotes.

Reply | Quote

Thanks again Pat. I knew it had to be the data. But I think I’ll try the crosstab as you suggested. Thanks again.

Reply | Quote

The only problem with the crosstab is the ordering of mmm/yy for the headers.

If you want these in the correct order you will have to build the pivot clause in the query prior to running the report. I would tend to do this from a form where you could also specify your range of dates.

Reply | Quote

He Pat: Thanks for all the help. I cleaned up the data (removing all ” and ‘); also had to use the nz function in the code to cover the situation where there was null quantities. All is well in Ohio, USA for now. Will begin the crosstab challenge over the weekend. Thanks again.

Reply | Quote

Base you crosstab on the query you have now.

Reply | Quote