Circular reference when using filed name in IIF query (1 Viewer)

D

Danick

Registered User.
Local time
Today, 03:38
Joined
Sep 23, 2008
Messages
351
I have two fields. One called "Mobile" and another called "PersonalCellular"

I'm trying to write a query where if the Mobile field is empty, it would be populated with the PersonalCellular field. So I tried this,

Code: 
Mobile: IIf(IsNull([Mobile]),[PersonalCellular],[Mobile])

This results in an error message that reads, "Circular reference caused by alias 'Mobile' in query definition's SELECT list"
This is because the field Mobile is an actual field in the table. So I then tried this:

Code: 
MobileX: IIf(IsNull([Mobile]),[PersonalCellular],[Mobile])

This works fine. But I don't want to see MobileX in the results since I'm exporting the query to a spreadsheet which needs to have a column called Mobile (not MobileX).
Is there any other way to be able to do this?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 00:38
Joined
Oct 29, 2018
Messages
21,454
Hi. The only way I could think of is to use another query on top of your current query, so you can change the name back.
 
D

Danick

Registered User.
Local time
Today, 03:38
Joined
Sep 23, 2008
Messages
351
theDBguy said:
Hi. The only way I could think of is to use another query on top of your current query, so you can change the name back.
Click to expand...

I was hoping there was another way...:(
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 08:38
Joined
Sep 21, 2011
Messages
14,232
What happens if you qualify the field name with it's table name?
 
bob fitz

bob fitz

AWF VIP
Local time
Today, 08:38
Joined
May 23, 2011
Messages
4,719
Try:
Code: 
SELECT YourTableName.Mobile AS M, YourTableName.PersonalCellular AS PC, IIf(IsNull([M]),[PC],[M]) AS Mobile
FROM YourTableName;
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 00:38
Joined
Oct 29, 2018
Messages
21,454
bob fitz said:
Try:
Code: 
SELECT YourTableName.Mobile AS M, YourTableName.PersonalCellular AS PC, IIf(IsNull([M]),[PC],[M]) AS Mobile
FROM YourTableName;
Click to expand...
Very nice! Thanks!
 
D

Danick

Registered User.
Local time
Today, 03:38
Joined
Sep 23, 2008
Messages
351
bob fitz said:
Try:
Code: 
SELECT YourTableName.Mobile AS M, YourTableName.PersonalCellular AS PC, IIf(IsNull([M]),[PC],[M]) AS Mobile
FROM YourTableName;
Click to expand...

Not sure where to put this. Can't get it to work in the query
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 00:38
Joined
Oct 29, 2018
Messages
21,454
bob fitz said:
Your Welcome ;). Let's leave the cigars unlit until we've heard from the OP though :D
Click to expand...
I tested it out of curiosity and it worked for me! However, I haven't really ran into any issues where I needed it, but at least now I won't have to worry if I do.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 08:38
Joined
Sep 21, 2011
Messages
14,232
Danick said:
Not sure where to put this. Can't get it to work in the query
Click to expand...

I'd be keen to know if just qualifying the fields in your IIF function would work as well?
 
D

Danick

Registered User.
Local time
Today, 03:38
Joined
Sep 23, 2008
Messages
351
bob fitz said:
What is the name of your table?
Click to expand...

the table name is called tblContacts

Here is the full sql for that query. This is working fine when I changed the name to "Mobil" Just can't get it work as "Mobile" since it's the same name as in the table.

Code: 
SELECT tblCompany.CompanyName AS Company, tblContacts.ContactName AS [Contact Name], tblContacts.Title AS [Contact Title], tblCompany.CompanyModel AS [Program Supported], tblContacts.[Contact Level], tblCompany.CompanyCountry AS Country, IIf([TelNoExt] Is Null,[TelNo],[TelNo] & "  x " & [TelNoExt]) AS [Office Telephone], IIf(IsNull([Mobile]),[PersonalCellular],[Mobile]) AS Mobil, tblContacts.FaxNo AS [Fax Number], tblContacts.email AS Email, IIf([Contact Level]="Executive Level","1",IIf([Contact Level]="Management Level","2",IIf([Contact Level]="Working Level","3",Null))) AS Sort, IIf([Contact Title] Like "*Owner*",7,IIf([Contact Title] Like "*CEO*",7,IIf([Contact Title] Like "President*",7,IIf([Contact Level]="Executive Level","6",IIf([Contact Level]="Management Level","5",IIf([Contact Level]="Working Level","4",3)))))) AS SortOrg, tblCompany.ICAO AS Code, tblCompany.RSO, tblContacts.DateUpdated AS [Rev Date], Null AS [Escalation Level]
FROM tblCompany LEFT JOIN tblContacts ON tblCompany.CompanyID = tblContacts.CompanyID
WHERE (((tblContacts.Flag)=Yes) AND ((tblCompany.CompanyActive)=Yes))
ORDER BY tblCompany.CompanyName, tblContacts.ContactName;

I know it's quite long, but I wan't sure how to separate just the part that I'm having problems with.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 00:38
Joined
Oct 29, 2018
Messages
21,454
Gasman said:
I'd be keen to know if just qualifying the fields in your IIF function would work as well?
Click to expand...
Fair enough. I just gave it a try and didn't get any errors. However, if two columns with the same name are to be displayed, at least in my limited experience, one of them will show up empty.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 00:38
Joined
Oct 29, 2018
Messages
21,454
Danick said:
the table name is called tblContacts

Here is the full sql for that query. This is working fine when I changed the name to "Mobil" Just can't get it work as "Mobile" since it's the same name as in the table.

I know it's quite long, but I wan't sure how to separate just the part that I'm having problems with.
Click to expand...
Okay, let's see if this works (based on Gasman's suggestion):
Code: 
SELECT tblCompany.CompanyName AS Company, tblContacts.ContactName  AS [Contact Name], tblContacts.Title AS [Contact Title],  tblCompany.CompanyModel AS [Program Supported], tblContacts.[Contact  Level], tblCompany.CompanyCountry AS Country, IIf([TelNoExt] Is  Null,[TelNo],[TelNo] & "  x " & [TelNoExt]) AS [Office  Telephone], [b]IIf(IsNull(tblContacts.[Mobile]),tblContacts.[PersonalCellular],tblContacts.[Mobile]) AS Mobile[/b],  tblContacts.FaxNo AS [Fax Number], tblContacts.email AS Email,  IIf([Contact Level]="Executive Level","1",IIf([Contact  Level]="Management Level","2",IIf([Contact Level]="Working  Level","3",Null))) AS Sort, IIf([Contact Title] Like  "*Owner*",7,IIf([Contact Title] Like "*CEO*",7,IIf([Contact Title] Like  "President*",7,IIf([Contact Level]="Executive Level","6",IIf([Contact  Level]="Management Level","5",IIf([Contact Level]="Working  Level","4",3)))))) AS SortOrg, tblCompany.ICAO AS Code, tblCompany.RSO,  tblContacts.DateUpdated AS [Rev Date], Null AS [Escalation Level]
FROM tblCompany LEFT JOIN tblContacts ON tblCompany.CompanyID = tblContacts.CompanyID
WHERE (((tblContacts.Flag)=Yes) AND ((tblCompany.CompanyActive)=Yes))
ORDER BY tblCompany.CompanyName, tblContacts.ContactName;
 
bob fitz

bob fitz

AWF VIP
Local time
Today, 08:38
Joined
May 23, 2011
Messages
4,719
Can you post a copy of the db wit just the necessary table(s) and query
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 08:38
Joined
Sep 21, 2011
Messages
14,232
When I first started with Access, all my autonumber fields were called ID. :eek:

Then I found I could get past that by qualifying the field names.?

Bob has done much the same as I was thinking of, just in a different way.?

Curiousity now, that's all.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 00:38
Joined
Oct 29, 2018
Messages
21,454
Gasman said:
When I first started with Access, all my autonumber fields were called ID. :eek:

Then I found I could get past that by qualifying the field names.?

Bob has done much the same as I was thinking of, just in a different way.?

Curiousity now, that's all.
Click to expand...
Hi. But if you have the following:
Code: 
SELECT ID, TableName.ID AS ID

FROM TableName
I am saying the second ID column will be empty.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 08:38
Joined
Sep 21, 2011
Messages
14,232
Also try Bob's version
Auuming the phone numbers are in tblContacts

HTH
Code: 
SELECT tblCompany.CompanyName AS Company, tblContacts.ContactName AS [Contact Name], tblContacts.Title AS [Contact Title], tblCompany.CompanyModel AS [Program Supported], tblContacts.[Contact Level], tblCompany.CompanyCountry AS Country, IIf([TelNoExt] Is Null,[TelNo],[TelNo] & "  x " & [TelNoExt]) AS [Office Telephone],tblContacts.Mobile as M, tblContacts.PersonalCellular AS PC, IIf(IsNull([M]),[PC],[M]) AS Mobile, tblContacts.FaxNo AS [Fax Number], tblContacts.email AS Email, IIf([Contact Level]="Executive Level","1",IIf([Contact Level]="Management Level","2",IIf([Contact Level]="Working Level","3",Null))) AS Sort, IIf([Contact Title] Like "*Owner*",7,IIf([Contact Title] Like "*CEO*",7,IIf([Contact Title] Like "President*",7,IIf([Contact Level]="Executive Level","6",IIf([Contact Level]="Management Level","5",IIf([Contact Level]="Working Level","4",3)))))) AS SortOrg, tblCompany.ICAO AS Code, tblCompany.RSO, tblContacts.DateUpdated AS [Rev Date], Null AS [Escalation Level]
FROM tblCompany LEFT JOIN tblContacts ON tblCompany.CompanyID = tblContacts.CompanyID
WHERE (((tblContacts.Flag)=Yes) AND ((tblCompany.CompanyActive)=Yes))
ORDER BY tblCompany.CompanyName, tblContacts.ContactName;
 
You must log in or register to reply here.

Similar threads

M
add more digits on the Mobile Number field
Replies
2
Views
249
Gasman
Gasman
B
Query Expression IIf Statement
Replies
8
Views
411
Pat Hartman
P
T
Solved Fill in form fields based on a combo box.
Replies
5
Views
578
Thel888
T
T
Basic Web Access to Small Data
Replies
7
Views
531
GPGeorge
GPGeorge
shamal
Number of empty values.
Replies
6
Views
202
ebs17
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom