# Thread: iif expression not working the way i want it to

1. Registered User
Join Date
Mar 2012
Posts
68

## Unanswered: iif expression not working the way i want it to

Here's the expression:

507 Totals After Tax: IIf([Items for Job Transfer]![class]="1" And [items for job transfer]![tax status]="t",Nz([quantity])*Nz([unit price])*1.06,Nz([quantity])*Nz([unit price]))

"class" field consists of 2 numbers. 1 and 2
What im after:

if "class" = 1 and "tax status" = t then qty*price*1.06
if "class" = 1 and "tax status" = t/e then qty*price

This would be one field
The other would be

if "class" = 2 and "tax status" = t then qty*price*weight*1.06
if "class" = 2 and "tax status" = t/e the qty*price*weight

i'm placing the expression into a query if it matters. Thanks in advance for help.

2. Registered User
Join Date
May 2004
Location
New York State
Posts
1,178
if "class" = 1 and "tax status" = t then qty*price*1.06
if "class" = 1 and "tax status" = t/e then qty*price

This would be one field
The other would be

if "class" = 2 and "tax status" = t then qty*price*weight*1.06
if "class" = 2 and "tax status" = t/e the qty*price*weight
1-Are Class 1 and Class 2 in two seperate fields, or two possible values for the same field?
2-What's the difference whether the class is 1 or 2? The formula is the same.
3-Is class a string variable in the table or is it an integer? I notice you put "" marks around it ("1") in your statement.

To the point: if class 1 and 2 have the same formula, why put it in the iif() condition in the first place?
Also, even if classes 1 and 2 have different formulas (not like in your posting), you can put it all into one complex iif() statement, as follows:
Code:
`507 Totals After Tax: IIf([Items for Job Transfer]![class]="1", IIf([items for job transfer]![tax status]="t",Nz([quantity])*Nz([unit price])*1.06,Nz([quantity])*Nz([unit price]),<follow by the formula for class 2 using the same iif() format as before, iif(items for job transfer]![tax status]="t", etc) and close with an additional right parenthese)`
Sam

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
If I was you I would create a function in a module then what I need returning

4. Registered User
Join Date
Mar 2012
Posts
68
Originally Posted by sam
1-Are Class 1 and Class 2 in two seperate fields, or two possible values for the same field?
2 possible values for the same field.

Originally Posted by sam
2-What's the difference whether the class is 1 or 2? The formula is the same.
The formula is not the same. Value 2 needs to calculate unit weight where value 1 has no unit weight.

Originally Posted by sam
3-Is class a string variable in the table or is it an integer? I notice you put "" marks around it ("1") in your statement.
I'm new access and not quite sure what the difference is yet between an integer or string variable. 1 (or 2) represent the PK from tblClass. Knowing what I do I would have to guess a string variable.

Im going to try your suggestion about keeping it all one complex expression. I will repost the expression if I still have issues

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
put tax rates into a separate table NOT hardcoded into queries
it will bite you in the backside
when tax rates change its a user action (they own their data they chaneg their data so it doesn't require any developer action. usually you will be the last to be told of such a change and testing the change can take a long while.

I'd be tempted to flatten the process and set a weight of 1 so in effect instead of 4 separate equations you end up with 1, possibly two if you cannot set weight to 1 for class 1 items

6. Registered User
Join Date
Mar 2012
Posts
68
Heal,
I'll take your advice on the tax. Read that somewhere else too. This was kind of a temp solution for now. Also changing the weight to 1 may work. Thanks for the idea. Now I have to work on writing that code/expression

7. Registered User
Join Date
May 2004
Location
New York State
Posts
1,178
Good. Now you know why healdem is a moderator on the forum. He really has a lot to teach.

Also, if class 1 or 2 is an autonumber, it is not a string but a numerical type Long. Hence, it does not get quotes around it.

Sam

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•