IIF Statements

[Shannon]

I need to included two IIF statements in one. I can't use the AND because it is based on different results. Any help is much appreciated. Thanks Shannon

 

[Jack Cowley]

Give us a few more details on what you want to do and I am sure that someone here will be able to give you an answer.

 

[Shannon]

I am trying re-create this Excel Formula into access Code.

=IF(B43="53 Foot",(5000000/+IF($A24="Millimeters",(+B23*C23*D23),(+B24*C24*D24))),(4000000/+IF($A24="Millimeters",(+B23*C23*D23),(+B24*C24*D24))))

Thanks for the quick response!

 

[Pat Hartman]

IIf() functions can be nested.

IIf(condition,TruePart,FalsePart)

You can replace TruePart and/or FalsePart with another IIf(). So -

IIf(condition1,IIf(Condition2,TruePart2,FalsePart2),FalsePart1)

You could then replace FalsePart2 (for example) with another IIf(). I warn you though, this structure can be very difficult to read if it becomes too complex. In that case you are better off creating a public function where you can use Case statements or nested If's which will be easier to read and maintain.

 

[KKilfoil]

Sometimes it can be better (clearer) to take a complex calulation and break it down into smaller chunks. For example, in your query, you could do:

Denominator: iif([a24]="Millimeters", [B23]*[C23]*[D23],[B24]*[C24]*[D24])

Numerator: iif([B43]="53 Foot",5000000, 400000)

FinalCalc: [Numerator]/[Denominator]


If you do not like the clutter of the intermediate fields, uncheck their 'show' options in the query design grid to hide them in the query results.

 

[Shannon]

Thanks you so much! It worked perfect.