If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#Error on IIF Statement
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 |
#2
|
|||
|
|||
#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 |
#3
|
|||
|
|||
#Error on IIF Statement
John Spencer wrote: You can nest IIF statements or you can use the Switch function The Switch function is a little easier to understand, but is not "native" to sql SWITCH() is as "native" to Jet SQL as IIF(). Neither is present in the ANSI SQL standards (the equivalent of which is CASE) but both are "native" to Jet 4.0. Unlike the scalar functions that were introduced into VBA6 (Replace(), for example, is much missed in Jet SQL) SWITCH was present in VBA5, hence is "native" to Jet SQL. Jamie. -- |
Thread Tools | |
Display Modes | |
|
|