Bypass conditions with another condition

Mackbear · Aug 28, 2019

Hello everyone, good day! I hope you're all doing fine.

So I have this formula because of your help guys and I need to add another condition that will ignore these conditions if the condition is met.
This one determines the priority based on Age, however I need to have it ignore this based on a content of a different field not on Age. I don't know how to structure it... hope you can help me

IIf([Age]<=-2,"Emergency",IIf([Age]<=0,"Critical",IIf([Age]<=2,"High",IIf([Age]<=5,"Medium",IIf([Age]>=6,"Low","No Date")))))

Thank you everyone in advance!

CJ_London · Aug 28, 2019

I presume this is in a query, not part of vba code?

just put the other condition first - but the iif function needs to return something, even if it is a "" or null

iif(othercondition=true,"",iif([Age]<=-2,"Emergency.........

isladogs · Aug 28, 2019

Just out of interest how can Age ever be a minus value such as -2?

Mackbear · Aug 29, 2019

isladogs said:
Just out of interest how can Age ever be a minus value such as -2?

Hello, it is just the amount of days that it is already past, it is calculated based on a date, if it is -2 then it is 2 days past due.

Mackbear · Aug 29, 2019

CJ_London said:
I presume this is in a query, not part of vba code?

just put the other condition first - but the iif function needs to return something, even if it is a "" or null

iif(othercondition=true,"",iif([Age]<=-2,"Emergency.........

Thank you very much for helping out a beginner... thank you!

Mackbear · Aug 29, 2019

CJ_London said:
I presume this is in a query, not part of vba code?

just put the other condition first - but the iif function needs to return something, even if it is a "" or null

iif(othercondition=true,"",iif([Age]<=-2,"Emergency.........

Thank you this worked. Thank you for helping a beginner

theDBguy · Aug 29, 2019

Mackbear said:
Thank you this worked. Thank you for helping a beginner

Hi. Just as FYI, what you're doing now can be called "Nesting." This is a very common practice. You sometimes need to "nest" several functions or expressions to modify the outcome of a simple expression. For example, let's say you have a start date and you want to know how many days before its anniversary date. You might try something like this:

Code:

DateDiff("d",Date(),DateAdd("yyyy",1,[StartDate]))

What that code is doing is add 12 months (1 year) to the "start date" to get the anniversay date. Then, it compares the resulting anniversary date to today's date to get the number of days remaining.

Good luck with your project.

CJ_London · Aug 29, 2019

something else to be aware of - the iif function when used is a query only needs to return the true part (the first option). However in VBA it needs to return both.

If a value is not returned then it will have a 'value' of null. Whether this matters or not to you depends on what you are doing with the data. So I could have said

iif(othercondition<>true, iif([Age]<=-2,"Emergency.........

Mackbear · Aug 30, 2019

CJ_London said:
something else to be aware of - the iif function when used is a query only needs to return the true part (the first option). However in VBA it needs to return both.

If a value is not returned then it will have a 'value' of null. Whether this matters or not to you depends on what you are doing with the data. So I could have said

iif(othercondition<>true, iif([Age]<=-2,"Emergency.........

Thanks it would have been any of the conditions if the first condition is not true...

Bypass conditions with another condition

Mackbear

Registered User.

CJ_London

Super Moderator

isladogs

MVP / VIP

Mackbear

Registered User.

Mackbear

Registered User.

Mackbear

Registered User.

theDBguy

I’m here to help

CJ_London

Super Moderator

Mackbear

Registered User.

Similar threads

Users who are viewing this thread