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

Rabu, 19 Juni 2019

Re: [MS_AccessPros] Nested IIF statement issues

Glad it helped. It always makes sense to step away from the code and capture the exact logic prior to building complex expressions/code.

Then start with a simple proof of concept. For instance, use only 2 levels and get it right. When 2 levels work as desired then add more levels.

Finally, make sure you document. Queries are horrible if not impossible to document inline. That's why I typically prefer to create a small user-defined function that can be commented and used over and over and over. This keeps your business logic/code in one place.

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, June 19, 2019 9:45 AM
To: Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]
Subject: 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;