Multiple IIf expressions in a form - please help

[noaccess]

Hi,



I am really new to Microsoft Access and have tried to get a multiple IIf statement to work in a Form but it doesn't. What I want it to do is:

If Qualifying Car is true and sale price is 20% VAT then *11/66. If qualifying car is true and sale price is 17.5% VAT then *7/47. If qualifying car is false and sale price is 20% then profit *11/66. If qualifying car is false and sale price is 17.5% then profit *7/47. If profit is less than zero then render zero.

I wrote this...

=IIf([Qualifying Car]=True And [Selling VAT rate]="20",[Sale Price]*11/66,IIf([Qualifying Car]=True And [Selling VAT rate]="17.5",[Sale Price]*7/47,IIf([Qualifying Car]=False And [Selling VAT rate]="20",[Profit]*11/66,IIf([Qualifying Car]=False And [Selling VAT rate]="17.5",[Profit]*7/47))))

It doesn't work and nor do I know how to get it to show zero if the Profit is less than zero.

I have now spent days and days trying to make it happen but I just can't.

If anyone can help then I would be truly grateful.

Thanks in advance,

Sarah

[RuralGuy]

Have you tried using a User Defined Function (UDF) in a Standard Module for the function? It would be easier to understand and maintain later, especially if you used a Select Case structure. Just pass it both values.

[noaccess]

Thanks for your advice but unfortunately I don't know what a UDF is. I have since looked on the Help section and seen that it is a macro but I know even less about macros than IIf functions.

Is there really know way to write it as a multiple IIf that will work?

[ajetrumpet]

have you seen this? https://www.accessforums.net/tutoria...nts-14417.html

breaking it out, you see this:

IIf([Qualifying Car]=True And [Selling VAT rate]="20",
   ([Sale Price]*11/66), 
      IIf([Qualifying Car]=True And [Selling VAT rate]="17.5",
         ([Sale Price]*7/47),
            IIf([Qualifying Car]=False And [Selling VAT rate]="20",
               ([Profit]*11/66),
                  IIf([Qualifying Car]=False And [Selling VAT rate]="17.5",
                     ([Profit]*7/47)))))

so that will tell you right there where the issue is. You have NO false argument in your last nested IIF(). The way you have it written now, it should look like:

IIf([Qualifying Car]=True And [Selling VAT rate]="20",
   ([Sale Price]*11/66), 
      IIf([Qualifying Car]=True And [Selling VAT rate]="17.5",
         ([Sale Price]*7/47),
            IIf([Qualifying Car]=False And [Selling VAT rate]="20",
               ([Profit]*11/66),
                  IIf([Qualifying Car]=False And [Selling VAT rate]="17.5",
                     ([Profit]*7/47), value if none of above satisfy))))

But it would be easier to read if you just wrote:

IIf([Qualifying Car]=True
   IIF([Selling VAT rate]="20",
      ([Sale Price]*11/66), 
         IIF([Selling VAT rate]="17.5",
            ([Sale Price]*7/47),
               [value if qualifying car and vat rate <> above values])),
IIf([Selling VAT rate]="20",
   ([Profit]*11/66),
      IIf([Selling VAT rate]="17.5",
         ([Profit]*7/47), value if NOT qualifying care and vat rate <> above values)))

But really what you should do is put this in VBA, under a form's record event of somekind because it would greatly reduce the text you have to look at. See the FAQ for how to do that. Also, you should really wrap multiple math-based operations in (). Simply due to order of ops if it applies, but in your calcs above it does not. It's always easier to read though.

[noaccess]

Thanks again for all your help...I have done that and now it works fine

[ajetrumpet]

Yay! I don't like to intrude on Allan's post, but I kinda think my FAQ is useful. so of course I had to boast!

good luck to ya.

[khalid]

[QUOTE=ajetrumpet;63521]have you seen this? https://www.accessforums.net/tutoria...nts-14417.html

breaking it out, you see this:

IIf([Qualifying Car]=True And [Selling VAT rate]="20",
   ([Sale Price]*11/66), 
      IIf([Qualifying Car]=True And [Selling VAT rate]="17.5",
         ([Sale Price]*7/47),
            IIf([Qualifying Car]=False And [Selling VAT rate]="20",
               ([Profit]*11/66),
                  IIf([Qualifying Car]=False And [Selling VAT rate]="17.5",
                     ([Profit]*7/47)))))

nice way of presenting IIf() function