Expr1: Expr2: etc. appearing in queries

[Gina Maylone]

Hello everyone!

I keep getting these "broken" queries.

SELECT Account.Date AS Expr1, DatePart("m",[Date]) AS M, DatePart("yyyy",[Date]) AS Y, Account.Owner AS Expr2, Account.DBAmt AS Expr3, Account.CRAmt AS Expr4
FROM Account
WHERE (((DatePart("m",[Date]))<DatePart("m",[Forms]![AReports]![SDate])) AND ((DatePart("yyyy",[Date]))=DatePart("yyyy",[Forms]![AReports]![SDate]))) OR (((DatePart("yyyy",[Date]))<DatePart("yyyy",[Forms]![AReports]![SDate])));

Where it should be:

SELECT Account.Date, DatePart("m",[Date]) AS M, DatePart("yyyy",[Date]) AS Y, Account.Owner, Account.DBAmt, Account.CRAmt
FROM Account
WHERE (((DatePart("m",[Date]))<DatePart("m",[Forms]![AReports]![SDate])) AND ((DatePart("yyyy",[Date]))=DatePart("yyyy",[Forms]![AReports]![SDate]))) OR (((DatePart("yyyy",[Date]))<DatePart("yyyy",[Forms]![AReports]![SDate])));

I thought it was happening when I disconnected the backend from the front end, but that isn't the case (it happened without disconnecting as well). Has anyone had this experience? Is there a reason and/or solution? Is it corruption?

Thank you! Hope you're having a beautiful day!

[isladogs]

I've definitely had this happen with split databases where for whatever reason the external connection is 'broken' when trying to run the query.

[ranman256]

and check the field labels in the queries:

EXPR1: field, EXPR2: field,

Account.Owner AS Expr2, Account.DBAmt AS Expr3

[ssanfu]

Hi, Gina.

Another thing that will cause this behavior is if the table name was changed. Example would be if table "Accounts" was renamed to "Account" (no 's' at the end).


BTW, "Date" is a reserved word (and a built in function) in Access and shouldn't be used for an object name.
See Problem names and reserved words in Access

[Gina Maylone]

Thanks Steve! No table names have been changed. I inherited the db and am waiting for them to approve a new one so I can eliminate the poor data structure and field name problems. :-) Hope you are well.

[Gina Maylone]

Thanks ranman, if I'm following what you're saying, the db does that itself (

Owner AS Expr2, Account.DBAmt AS Expr3

). Not sure what else I should check? Thanks again!

[ranman256]

you can name it whatever you wish. Change Expr1 to MyField1, or whatever.

[Micron]

I'm leaning towards what's in post 4. What would seem to be the mixing of the use of the word "date" (syntax) may be making it even worse. Sometimes it seems it's a field, yet there's no form reference.
DatePart("m",[Date]))<DatePart("m",[Forms]![AReports]![SDate]

If that is the case and this is a query, it cannot know what [Date] is. If it's vba constructed sql, that might explain why nothing was said about parameter prompts.If the table field is named Date, the syntax should be Account.[Date], not Account.Date. IMHO, the best thing to do in this new db is make sure no objects used reserved words, assuming this db isn't worth fixing if it's being replaced.

[ssanfu]

As Colin said, any time the query can't find one of the tables in the query, Access automatically adds "AS ExprX" (where "X" is a number) as the name of the column in the query when looking at the query in SQL view.
In SQL view, all you need to do if the table is a valid table) is delete the "AS ExprX". In design view, you would need to delete "ExprX:".

This behavior also happens if the name of a field is not found in the the query table(s).