A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

#Error on IIF Statement



 
 
Thread Tools Display Modes
  #1  
Old January 3rd, 2007, 05:38 PM posted to microsoft.public.access.queries
RaeRae
external usenet poster
 
Posts: 4
Default #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  
Old January 3rd, 2007, 05:56 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default #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  
Old January 4th, 2007, 10:16 AM posted to microsoft.public.access.queries
Jamie Collins
external usenet poster
 
Posts: 1,705
Default #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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:22 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.