Update query changes field names (1 Viewer)

[ButlerRick]

I have a simple update query that sets the existing values of all the fields in a specific record in a table to Null. However; for some reason the query replaces the field names with expressions such as Exp1:MyFieldName1, Exp2:MyFieldName2, Exp3:MyFieldName3, etc. Can anyone explain why this happens. (The fields are various field types.)

 

[pbaldy]

At some point design view couldn't find/resolve the field name. Believing it to be wrong, it did what you see. Is it possible the table was deleted temporarily and the query opened?

 

[The_Doc_Man]

EXPR1. EXPR2, etc. stand for "Expression 1", "Expression 2", etc.

If you have math in a particular reference, that makes it an expression that is no longer equal to the source field.

For instance, in the query design grid, if you have "=Date() + 1" then that would be an expression. Since you didn't have an "AS name" clause for that field, it would show up as an EXPR field.

If you have ANY MATH AT ALL, that would happen.

 

[ButlerRick]

At some point design view couldn't find/resolve the field name. Believing it to be wrong, it did what you see. Is it possible the table was deleted temporarily and the query opened?

This update query fires directly after an append query which copies the record to a history table. Perhaps the first query has the table occupied when the update query attempts to run.

 

[jdraw]

Please post the SQL for your UPDATE query.

 

[pbaldy]

This update query fires directly after an append query which copies the record to a history table. Perhaps the first query has the table occupied when the update query attempts to run.

I wouldn't expect that to cause this. You can try changing the names back to their actual names and see if it holds.

 

[ButlerRick]

I wouldn't expect that to cause this. You can try changing the names back to their actual names and see if it holds.

Yes; I have changed the file names back to their originals and they do hold for awhile. However; eventually they suddenly flip back to expressions.

 

[pbaldy]

I would pay attention to the circumstances when they flip back to expressions. In my experience, it will happen when you open the query when the underlying table isn't accessible. For example, I've got queries that operate on a temp table. If I happen to open the query and the temp table isn't there, the field names all get switched as you describe. That's Access throwing it's hands up and saying "I can't find TableName.MyFieldName1".