IIF or Switch or ??

[pj33558]

I know this is probably easy/peezy for everyone on here but after lots of research, I still can't get my calc to fly right.

I have a lengthy accounting list of subaccounts.

Currently, I have a table with percentages based upon type of food to eat, and how much gets paid to supplier.

So, for instance, if the animal is a horse and the supplier sends EQS, then the percentage paid is 5% x 12 months up front; if the animal is a zebra, and the supplier sends EQS, then the percentage is 5% but only 1 month, for every other kind it is EQT x 1 month.

My current IIF statement reads:

PCTERND: IIF([subDE]="15",[amount]*[animal-mff]*12,IIf([subDE]="16",[ amount]*[eqs], iif([subDE]=(), [ amount]*[ser])))



The piece in blue ink is NOT working. I need the calc to grab everything not 15 and not 16, and perform the calc.

I have tried: Switch([subDE]="15",[amount]*[animal-mff]*12,[subDE]="16",[ amount]*[eqs],[subDE] not in (“15”,”16”),[ amount]*[ser])

With SWITCH, I get #error; with the IIF I get $0 amounts for anything that is not 15 or 16 (otherwise, it works perfectly).

The name on the side of number field, is not used nor identified in the database, it is used by accounting. I simply added it for reference.

15	horse
16	zebra
17	donkey
25	lion
26	tiger
35	bear
57	fish
80	dog
81	hyena
82	wolf
83	coyote

[RuralGuy]

I believe IIF parses left to right so just leave off the last IIF and assume it is not 15 or 16. BTW, is the first fields numeric or text? You are treating it as text. No quotes needed for a numeric value.

[pj33558]

Great point @ "text" or "numeric" - the pull is direct via ODBC from G/L and I believe it comes in as text, hence my "" around the numbers... but I will check.

2nd - thank you - will try eliminating last IIF - ty.

[pj33558]

So, the "catch all" by eliminating last IIF, worked like it should.

However, now, it does not recognize "80". Should there be an "or" before that?

So: IIF([subDE]="15",[amount]*[animal-mff]*12,OR IIf([subDE]="16",[ amount]*[eqs], [ amount]*[ser])

[RuralGuy]

NO. You did not say anything about 80 before. If you want to test for it then add another IIF.

[pj33558]

Sorry, my bad. 80 is actual number in the field, not my hypothetical.

The formula is NOT recognizing the "16" nor performing the calculation.

I get the correct amount for 15 and for everything BUT "16" ... the highlighted piece is not being calculated at all.

So it reads: IIF([subDE]="15",[amount]*[animal-mff]*12,IIf([subDE]="16",[ amount]*[eqs], [ amount]*[ser])

[RuralGuy]

You don't have enough arguments but aside from that, have you tried eliminating the quotes to see if they are really numbers rather than text?

[pj33558]

Figured it out.

My field name was wrong: [ account] does not = [amount]

Thank you!

[pj33558]

have you tried eliminating the quotes to see if they are really numbers rather than text?

The ODBC link crashes when I try to open that table in design view to see what the properties are. I believe at one point, I tried without the "" and I got errors.