Help with IIF Statements...

[leeb1977]

Hi, I wonder if you could help me???

I've got an IIF statement in my query where:

Kilos Required: IIf([UM]="GM",[Quantity]/1000,[Quantity])

And, I want to put the following in:

Kilos Required: IIf([UM]="KG" And [Fixed or Variable Qty Code]="F",
[Quantity]/[Batch Qty],[Quantity])

Both work separately, but I want to combine the two.

Does this make sense? Is it possible?

Thanks!!

Lee

 

[Stefan Hoffmann]

hi,

I've got an IIF statement in my query where:
Kilos Required: IIf([UM]="GM",[Quantity]/1000,[Quantity])

And, I want to put the following in:
Kilos Required: IIf([UM]="KG" And [Fixed or Variable Qty Code]="F",
[Quantity]/[Batch Qty],[Quantity])

Both work separately, but I want to combine the two.
Does this make sense? Is it possible?

As they are separate branches, you may use Switch():

Kilos Required: Switch([UM]="KG", yourIifForKG, [UM]="GM", yourIifForGM)

btw, you should avoid using spaces in your field names.


mfG
--> stefan <--

 

[Douglas J. Steele]

Kilos Required: IIf([UM]="GM",[Quantity]/1000,IIf([UM]="KG" And [Fixed or
Variable Qty Code]="F",[Quantity]/[Batch Qty],[Quantity]))

You might also want to include a check that [Batch Qty] isn't 0, since
you'll get a division by zero error if it is.

Kilos Required: IIf([UM]="GM",[Quantity]/1000,IIf([UM]="KG" And [Fixed or
Variable Qty Code]="F",[Quantity]/IIf([Batch Qty] <> 0, [Batch Qty],
1),[Quantity]))