#Error on IIF Statement
Is Discount a number field? If so, then drop the quotes and express the
percentage as a decimal number.
IIF(Discount = 0,Quantity*100)
IIF(Discount = .25, Quantity*50)
You can nest IIF statements or you can use the Switch function
IIF(Discount=0,100,IIF(Discount=.25,50,
IIF(Discount=.325,27,IIF(Discount=.4,18,IIF(Discou nt=.46,5.4,Null))))) *
Quantity
The Switch function is a little easier to understand, but is not "native" to
sql
SWITCH(Discount=0,100,Discount=.25,50,Discount=.32 5,27,Discount=.4,18,Discount=.46,5.5,True,Null)
* Quantity
Since I did not know what you wanted to do if Discount was a value other
than the 5 specified, I just set the result to Null.
--
John Spencer
Access MVP 2002-2005, 2007
"RaeRae" wrote in message
...
I have a query that I need to have return a calculation for me. I'm not
sure
why I'm getting this error. Can someone help?
My fields have a Quantity, Unit Price, and Discount. I've created an
ExtPrice field that gives me the Unit Price - Discount * Quantity. Works
fine.
I now need to create fields that will calculate the commission. I have 5
possible commission rates to calculate. My understanding is that iif
expression's can not contain more than one argument. At least I couldn't
make
it work, if it is allowed. I decided to write an arguement for each
senario
instead. My expression is as follows.
Ret: IIf([Discount]="0.00%",[Quantity]*100)
DLR: IIf([discount]="25%",[quantity]*50)3
DLR+: IIf([discount]="32.5%",[quantity]*27)
DIS: IIf([discount]="40%",[quantity]*18)
DIS+: IIf([discount]="46%",[quantity]*5.4)
all return the #error
Thanks for your help
--
RaeRae
|