ACCESS FEED: Re: [MS_AccessPros] Nested IIF statement issues

Rabu, 19 Juni 2019

Re: [MS_AccessPros] Nested IIF statement issues

Thank You Duane. I spent all day with this yesterday. It saved me another day.

Jim Wagner

On Tuesday, June 18, 2019, 6:44:11 PM MST, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

It seems the first question you would ask is "is there a legitimate name in Level 1 and if so, return it". This suggests something like:

Switch([Level 1]<>"<Not Applicable>",[Level 1],[Level 2]<>"<Not Applicable>",[Level 2], etc.

Duane

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: Tuesday, June 18, 2019 4:55 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Nested IIF statement issues

Hello all,

I have a query that I am trying to get a progressive value based on the values in the fields.

I have a query that has Emplid and Level 1, Level 2, Level 3, Level 4, Level 5. The levels are the order of supervisors to Department managers.

Level 1 is the lowest level and level 5 is the highest. the levels have names in the field.

Some of the employees do not have several level managers in level 1, 2 or 3,. for example several employees only have 1 or even 2 managers in Level 4 and 5.

I have it really close in several different ways. I used a switch and IIF statements. But It always stops on Level 2 it seems like. How do i get the query to show the lowest level that does not = Not Applicable?

Below is my sql.

Thank You for any directions

Jim Wagner

SELECT tblReportsToHierarchy.[Level 1], tblReportsToHierarchy.[Level 2], tblReportsToHierarchy.[Level 3], tblReportsToHierarchy.[Level 4], tblReportsToHierarchy.[Level 5], Switch([Level 1]="<Not Applicable>",[Level 2],[Level 2]="<Not Applicable>",[Level 3],[Level 4]="<Not Applicable>",[Level 5],[Level 3]="<Not Applicable>",[Level 4]) AS Melody, IIf([Level 1]="<Not Applicable>",[Level 2],IIf([Level 2]="<Not Applicable>",[Level 3],IIf([Level 3]="<Not Applicable>",[Level 4],IIf([Level 4]="<Not Applicable>",[Level 5],[Level 3])))) AS Jim, IIf([Level 1]="<Not Applicable>",[Level 2],IIf([Level 2]="<Not Applicable>",[Level 3],IIf([Level 3]="<Not Applicable>",[Level 4],[Level 1]))) AS MemoTo
FROM tblReportsToHierarchy;

__._,_.___

Posted by: Jim Wagner <luvmymelody@yahoo.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

Visit Your Group