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

Gasman

Enthusiastic Amateur
Local time
Today, 20:55
Joined
Sep 21, 2011
Messages
14,047
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.

I just got an error saying unresolved/duplicate reference or something along those lines?
So now each ID field has it's own prefix as it should have had in the first place.:eek:
 

Danick

Registered User.
Local time
Today, 16:55
Joined
Sep 23, 2008
Messages
351
Okay, let's see if this works (based on Gasman's suggestion)

Well this seems to be working. Had to fix a couple of names that somehow got lost in the translation... Going to test it a bit to make sure before implementing. Thanks for the help!!

This is what seems to have solved the problem

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

Gasman

Enthusiastic Amateur
Local time
Today, 20:55
Joined
Sep 21, 2011
Messages
14,047
Do try Bob's version as well please.?

The code I posted should work (I hope).:)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:55
Joined
Oct 29, 2018
Messages
21,358
I just got an error saying unresolved/duplicate reference or something along those lines?
So now each ID field has it's own prefix as it should have had in the first place.:eek:
That's interesting. I didn't get any errors.





 

Attachments

  • query.PNG
    query.PNG
    2.8 KB · Views: 210
  • result.PNG
    result.PNG
    4.4 KB · Views: 214

theDBguy

I’m here to help
Staff member
Local time
Today, 13:55
Joined
Oct 29, 2018
Messages
21,358
Well this seems to be working. Had to fix a couple of names that somehow got lost in the translation... Going to test it a bit to make sure before implementing. Thanks for the help!!

This is what seems to have solved the problem

Code:
Mobile: IIf(IsNull(tblContacts.[Mobile]),tblContacts.[PersonalCellular],tblContacts.[Mobile])
Hi. Glad to hear it worked for you. We all learned something. Good luck with your project.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:55
Joined
Oct 29, 2018
Messages
21,358
Do try Bob's version as well please.?

The code I posted should work (I hope).:)
Yes, it should work too, but I didn't see the need for it because the OP is not displaying the same column twice.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:55
Joined
Sep 21, 2011
Messages
14,047
That's interesting. I didn't get any errors.






Oops, I was talking about fieldnames in both tables being called ID, not using an alias.
Also I see what you mean about the second field being empty.
 

bob fitz

AWF VIP
Local time
Today, 20:55
Joined
May 23, 2011
Messages
4,717
Can you post a copy of the db with the table and your query.
 

bob fitz

AWF VIP
Local time
Today, 20:55
Joined
May 23, 2011
Messages
4,717
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.
Try the following SQL for your query:
Code:
SELECT tblCompany.Mobile AS M, tblCompany.PersonalCellular AS PC 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([M]Is Null,[PC],[M]) 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;
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 20:55
Joined
Jan 14, 2017
Messages
18,186
In your WHERE clause, what are the datatypes of those two fields?
If Boolean, I would use True instead of Yes. If text, the criteria should be "Yes"
 

Users who are viewing this thread

Top Bottom