john8217
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ryan Chong
It's a default feature in Access, to standardize your query structure by adding an alias name for those derived fields.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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]ASKER
Thanks everyone.