I was working in Access the other day, when I built a query with the expression you see here in this screenshot.
.
I’m passing the [Profile Number] field through the RIGHT function to pad it with 10 zeros.
When I try to run the query, I get this error telling me that a Circular Reference is caused by the alias I’m using. Hmm….I didn’t even know you could get a Circular Reference in Access.
.
Apparently, Access doesn’t like the fact that I’m referencing the [Profile Number] field then using the same field name as the alias.
.
Microsoft Support tells me to:
“Change the name of the alias or label used by the calculated expression or let Microsoft Access automatically assign an alias or label.”
.
But I like my alias. I want my alias. I shouldn’t have to change my alias for anyone!
No Microsoft – I’ll fix this problem my way.   Â
.
Instead of changing my alias, I simply added a reference to the table name in my expression.
.
This sufficiently distinguishes the field name from the alias.
No more Circular Reference error!
Holy alias Batman!
I’ve came across this from time to time. A week or two ago I couldn’t remember how I worked it out the last time so I decided to put the project aside for a while.
Thanks for the answer; you are the pigglety-wigglety bomb.
Yup, those aliases do make it easy/hard sometimes. This may be more a question for an entire blog post, but when to use [table].[field], and when to use [table]![field]. I’ve used both in my queries, but never know what’s more appropriate.