15 IIF Statements in a Query

[hiker8117]

I have to put in 15 nested IIF statements in a query and I know the maximum is 7.

I have an idea about creating a VBA module but not sure of the VBA code and type of module that needs to be created and how it will work in conjunction with the query.

I created an alias field in the query called Category and the IIF statements are:

Category: IIF([Group] =’’100” and [DO] <>””,”Support”, IIF([Group]=”300” and [DO] =”440”,” Support”, IIF([Group]="700" and [DO]<>"","Support","Prod"))).

I have 12 sets of criteria to enter and know there is a max of 7 IIF statements.



Any help is appreciated.

Regards

Gene

[June7]

Try Switch() function instead.

Category: Switch([Group]="100" And [DO]<>"","Support", [Group]="300" And [DO]="440","Support", [Group]="700" And [DO]<>"","Support", True,"Prod")

Or use OR. You show three criteria that return the same value.

Category: IIf(([Group]="100" And [DO]<>"") OR ([Group]="300" And [DO]="440") OR ([Group]="700" And [DO]<>""), "Support", "Prod")

Category: Switch(([Group]="100" And [DO]<>"") OR ([Group]="300" And [DO]="440") OR ([Group]="700" And [DO]<>""),"Support", True,"Prod")

Are you sure [DO] can have empty string? I never allow empty strings in fields, therefore I would test for Null.

However, expression may be too long regardless so will need VBA custom function.

A better approach is to build a lookup table of these conditions.

[pbaldy]

I would consider using a table and joining to it in the query. It would have fields for Group and Do and the value to return. Much more dynamic/maintainable than a hard-coded formula or VBA function.

[Micron]

I would avoid the use of the reserved word Group.
If the values are dynamic, such that a table or hard coded values are not suitable, a function that accepts the value(s) is probably required.

[orange]

I would like to see a clear statement of the requirement in plain English.
15 nested IIFs seems like a decision of how to do something, but what exactly is the something in clear terms.
Could be that the nested IIFs are required, but as you point out 7 seems to be the limit--- so my question stems from
"why is your requirement beyond the defaults/limits of Access??"

Perhaps there is more info regarding WHAT you need to achieve that can be used in deciding HOW (options) to do it in Access.

[hiker8117]

Thank you for your response. There are 15 groups that are unique to this file and the only Group "300" has a unique code in the DO field of "400". All of the other 14 Groups can a have a DO code from 001 up to 750. There are no null values in any of the DO fields
which is why I put in <>"" because I wanted to catch every record in the other 14. I am looking to populate in the alias category field
Support Services else Prod which is for production.

Again

Thanks for you response and will give this a try.

Regards

Gene

[hiker8117]

Thank you for your response

[hiker8117]

Great Idea..thank you for your response

Regards

Gene

[Micron]

FYI: Null is not the same as "" and you cannot compare Null to anything. Thus it cannot be =, < or any other comparison.
Without acknowledging who you are responding to, it's impossible to know which "idea" you refer to.

[orange]

@hiker8117
I noticed that in your post #1 in your expression you are showing different characters in your "double quotes" (eg "", ””). This may just be how you have typed your post. It may not be relevant to your question, but I have had issues with different characters like this.
As I asked in my post #5, a clear description of your requirement in plain simple English would be helpful. The key is simple, plain English with no database terms nor jargon. Tell us what your "business" is using terms that you would use to tell an 8 year old who knows nothing of you, your environment or database. Knowing what you are trying to achieve is critical before suggesting how it might be done.

Perhaps you could also post a copy of your tables and relationships as a jpg/png.

Good luck.