Query has "Expr" prefix in the field name

We had to change the name of a table. All the existing queries were obviously still pointing to the old table name. For each query, I opened it in SQL view, cut the code and pasted it into Word to use Word's "replace" feature to change all references to that old table name to the new table name. I then pasted the code back into the query's SQL view.
     Then I noticed something odd; I opened those queries in Design View and in the "Field" row, each column had a sequential prefix ("Expr1:" "Expr2:" "Expr3:", etc.). I deleted the prefixes and everything works fine. But does anyone know why those prefixes appeared?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Access is sometimes not as smart as it thinks it is.  In this case, you had opened the query at a point in time that the table was missing or renamed essentially orphaning all the selected columns.  So Access "helped" you by changing the column names to Expr 1, Expr 2, etc.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
It's a default feature in Access, to standardize your query structure by adding an alias name for those derived fields.
Gustav BrockCIOCommented:
It's neither default, nor smart, nor to be helpful.

The reason is, as Pat mentions, "you had opened the query at a point in time that the table was missing or renamed essentially orphaning all the selected columns" and - at that time - Access had no other choice than to include these as expressions.

The not-so-smart feature is, that Access is not able to revert this once the table is again present. There is no solution other than what you did - remove these alias.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Alternatively, you could use a tool that does this grunt work when renaming items.

You can go back to a pre-change backup of the database and write some VBA code to iterate the QueryDef items, looking for the table you are about to change (or just changed) and modify the 'affected' querys' SQL property.  I assume you'd use the Replace() function.

Once you have refreshed the QueryDefs collection, you would not see the Expr field names when you opened the query in the design window.
Hamed NasrRetired IT ProfessionalCommented:
A more convenient way, is to let query work and then modify by using old name as alias to new name.

SELECT [oldName].Field1 AS Expr1, [oldName].Field2 AS Expr2
FROM [NewName] AS [OldName]

Open in new window

john8217Author Commented:
Thanks everyone.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.