Alternatives to nested IIF statement in access query/Nested IIF not working correctly.

I have a nested IIF statement in my query and for some reason the last IIF nest isn't working if the value in the first expression is '0'. See below for the first expression. When I remove that expression it works fine. Can anyone see how I can fix this or possibly change this to a switch case or even a select case?

First Expression in the last IIF nest:
IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])<0.3

Open in new window



Entire Nested IIF Statement

IIf(IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])>=0.7 And (Round([fnusrqty1],0)>=250000),"A1", IIf(IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])>=0.3 And (Round([fnusrqty1],0)>250000),"A2", IIf(IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])<0.3 And (Round([fnusrqty1],0)>250000),"A3", IIf(IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])>=0.7 And (Round([fnusrqty1],0)>50000),"B1", IIf(IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])>=0.3 And (Round([fnusrqty1],0)>50000),"B2", IIf(IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])<0.3 And (Round([fnusrqty1],0)>50000),"B3",’C1’))))))

Open in new window

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Take a look at the Switch() function.

Switch(A=B, X, D=F, Y, T=H, Z)

You can have independent, unrelated expressions.
rspahitzCommented:
Are you sure that [fnusrqty1]=0?
Maybe it's null or something else that's not exactly zero?
Try showing that value separately to check it.

If it's null, you can try to add zero, which sometimes works:

[fnusrqty1]+0=0
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I looked at the switch function but how can I use 2 variables for each switch option?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
@rspahitz....I actually changed the query to pick up anything that was null and made it a 0 so it doesn't seem to fix the problem. But what I did notice is it doesn't seem to work when this part of the query result is 0. If the number is greater than 0 then it works fine.

IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"I looked at the switch function but how can I use 2 variables for each switch option?"
not quite following?
But my very generic example shows that.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Here is a bit more detail

https://msdn.microsoft.com/en-us/library/office/gg264257.aspx

Another example
Switch(A=B AND C=D, X, D=F AND (M=N OR Q=R), Y, T=H, Z)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rspahitzCommented:
Just as another thought, try reversing the condition (= -> <>) to see if it gives the expected results of 0:

IIf([fnusrqty1]<>0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])

And then maybe something that returns the original value rather than 0:

IIf([fnusrqty1]<>0,[fnusrqty1], "=0")

This might give more clues about what's going on.
Jeffrey CoachmanMIS LiasonCommented:
Just My opinion...
...as the previous experts have addressed your question directly...
When your IF's are this complicated, ...then you may wish to consider looking into using a VBA Function instead.
With a function you can:
-Have arguments
-Add Error handling
-Insert Comments
-Take advantage of the VBA debugging tools
-Have something that is "portable" to another system.

...and at least with a function, you can insert a message box to quickly test/see a condition, for example:
msgbox IIF(Expr1<Expr2,"Expr1 is less than Expr2","Expr1 is NOT less than Expr2")

IIf(IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])>=0.7 And (Round([fnusrqty1],0)>=250000),"A1", IIf(IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])>=0.3 And (Round([fnusrqty1],0)>250000),"A2", IIf(IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])<0.3 And (Round([fnusrqty1],0)>250000),"A3", IIf(IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])>=0.7 And (Round([fnusrqty1],0)>50000),"B1", IIf(IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])>=0.3 And (Round([fnusrqty1],0)>50000),"B2", IIf(IIf([fnusrqty1]=0,0,([bcslsCustomerInvoicesCurrentYear].[SumOfSumOfftotprice]/Format(Date(),"m"))*12/[fnusrqty1])<0.3 And (Round([fnusrqty1],0)>50000),"B3",’C1’))))))
...While some people would take troubleshooting an expression like this as a challenge, I just see it as something I'd rather not deal with.
;-)
It is always tricky getting the nesting just right., ...and one misplaced parenthesis can muck up the whole works...

Besides, ...at some point (if you add other equally complex expressions) Access may give you a "Expression too complex" error, ...then what?

JeffCoachman
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That's why I wanted to see what other alternatives there are to something like this. I was leaning towards a select case function. I think that would be the easiest route to go and it takes the involved nested function right out of the query all together. I'm actually working on building one now and will post back once I have it completed. Thanks for your opinion. I appreciate the input.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
The Switch Function is loosely equivalent to the the Select Case construct, except you can use Switch() in both SQL (eg queries) and VBA code. And using it directly in SQL then avoids the overhead of calling a function.
But really, it depends on how complex your situation is :-)

mx
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I wasn't aware you could use the switch function in VBA. That's a new function to me and I'm not that familiar with it. Let me do a little research on how to build the switch function in SQL. I'll post back.
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
ok I think I have the switch working but how do I add the last part of my nested IIF statement to it? The last false value of the nested statement should be "C1" when it doesn't equal any of the other options. Do I just put "C1" without a criteria at the end?
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I figured out what to do with the last value at the end. Here's what I came up with and it works pretty damn good too. And it's much cleaner than the nested IIF statement. Thanks for all your help everyone. I really appreciate it.


SPC: Switch([CY / Sales Potential]>=0.7 And [Sales Potential]>=250000,"A1",[CY / Sales Potential]>=0.3 And [Sales Potential]>250000,"A2",[CY / Sales Potential]<0.3 And [Sales Potential]>250000,"A3",[CY / Sales Potential]>=0.7 And [Sales Potential]>50000,"B1",[CY / Sales Potential]>=0.3 And [Sales Potential]>50000,"B2",[CY / Sales Potential]<0.3 And [Sales Potential]>50000,"B3",True,"C1")

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sure that works.  For the last argument you can also use:

1=1, <SomeValue>

I use  Switch() for a lot of different things.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Also for reference take a look at the Choose() function.  VERY handy when dealing with a number from 1 to N, for example with examining an Option Group code that is stored in a table.

Check it out :-)
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thanks for the info. I will check it out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.