Re: [MS_AccessPros] IIF expression question

 

Jim,

I think you can use an expression like the following to replace your nested IIf()s:

Nz([Ast Sup/Lead],  Nz(Supervisor, Nz([Asc/Ast Dir], [Dept Head]))) AS ReportingTo

Duane Hookom

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, October 2, 2017 12:30 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] IIF expression question

 

Hello all,

I have a query that I am trying to get the ReportingTo column to populate with the person that the employee reports to. I have a table that has fields as below. the Dept head is always populated but the Asc/Ast Dir field is sometimes blank as well as the Supervisor. So there are direct reports with no one between them and the dept head.

I have a query that gets most of the ReportTo column to populate but the missing Asc/Ast dir field that is not populated messing things up. I think I may have the fields in the wrong order or something.

SELECT SupervisorTable.[Person Nm], SupervisorTable.[Person Id], AccrualsForReport.sick, AccrualsForReport.comp, AccrualsForReport.vac, AccrualsForReport.[Accrual Proc Dt], AccrualsForReport.[Last Name], AccrualsForReport.[First Name], SupervisorTable.[Dept Head], SupervisorTable.[Asc/Ast Dir], SupervisorTable.Supervisor, SupervisorTable.[Ast Sup/Lead], AccrualsForReport.ReportsToName, AccrualsForReport.[Reports To], AccrualsForReport.ReportsToID, IIf(IsNull([Asc/Ast Dir]),[Dept Head],IIf(IsNull([Supervisor]),[Asc/Ast Dir],IIf(IsNull([Ast Sup/Lead]),[Supervisor],IIf(IsNull([Ast Sup/Lead]),[Supervisor])))) AS ReportingTo
FROM SupervisorTable INNER JOIN AccrualsForReport ON SupervisorTable.[Person Id] = AccrualsForReport.Emplid
WHERE (((SupervisorTable.[Person Id]) Not Like "1208509350"));

Dept Head

Asc/Ast Dir

Supervisor

Ast Sup/Lead

Thank You for any help

Jim Wagner

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___