Error for Transformed Row Source query on chart created automatically by Access

[MSommer]

I've created a database for users and split the database. I have a landing page or main form that auto opens when the database opens. In that form there are 4 charts based on simple queries. When I set the chart options Access automatically set the Transformed Row Source to:

SELECT Format([Effective Date], "mmm 'yy"), FROM [All Pending Enrollments_CHART] GROUP BY Format([Effective Date], "mmm 'yy"), Year([Effective Date])*12 + Month([Effective Date])-1 ORDER BY Year([Effective Date])*12 + Month([Effective Date])-1

Now when entering the database when the main form auto opens, users are presented with this error message:



The Select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.


Odd thing is that it seems to only present this error in the split database not the backup database that is not split. The select statement looks fine to me. Maybe something related to the splitting of the database? Any ideas are appreciated.

[ranman256]

try putting all your 'formatting' fields in the base query. Then the where & orderby is just a simple field call: order By [SortFld]

sometimes 'errors' on simple legal functions are actually a false error due to a bad REFERENCE in the VBE. Make sure your VBE references are good and none are MISSING.

sometimes access wants legal 'date math' by using: DateAdd() & DateDiff()

[pbaldy]

If that's the actual SQL, the comma before "FROM" would cause it to be illegal.

[MSommer]

try putting all your 'formatting' fields in the base query. Then the where & orderby is just a simple field call: order By [SortFld]

sometimes 'errors' on simple legal functions are actually a false error due to a bad REFERENCE in the VBE. Make sure your VBE references are good and none are MISSING.

sometimes access wants legal 'date math' by using: DateAdd() & DateDiff()

Didn't think about using the DateAdd and DateDiff functions. I will try that! Thank you

[MSommer]

Funny thing is I didn't write it. Access created that based on the selections I made in the chart formatting. I will try removing the comma. Thanks!

[Welshgasman]

You must have amended it, even if inadvertently.