Fixing Circular References in Access Queries

Consider a simple Vendor table with three fields:

• VendorID (Autonumber)
• VendorName (Required text field)
• Website (Optional text field)

Test Case

Let's say you have a simple query like the following:

Replacing NULLs with a Default Value

Notice that "Smelly Cat Songwriting" does not have a website.  So, for that record, the Website field will have a value of NULL.  

If you want to replace all the NULL values with a default message, you can do that with the following simple expression:

IIf(Website Is NULL, "{UNKNOWN}", Website)

The IIf operator first checks to see if the Website field is NULL.  If it is, it returns the literal string "{UNKNOWN}", otherwise it returns the value in the Website field.  So far, so good.

Keeping the Original Field Name

But, what if you want to keep the field name the same?  That is to say, what if we wanted our third field to be "Website" and not "Expr1"?

If we just change "Expr1" to "Website", we'll get a circular reference error:

Circular reference caused by alias 'Website' in query definition's SELECT list.

Avoiding the Circular Reference Error

To avoid the circular reference error, we simply use the fully-qualified field name within our expression.

Don't let the technical term "fully-qualified field name" confuse you.  All it means is that we prefix the field name with the table (or table alias) that the field comes from plus a period.  

So, in this case, we change Website to Vendor.Website.  

And now everything works with no error message:

It's so easy a Midshipman could do it!

Image by A_Different_Perspective from Pixabay