RE: How "large" can a IIf be in a query?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ted Allen (TedAllen_at_discussions.microsoft.com)
Date: 03/15/05 

Date: Tue, 15 Mar 2005 13:29:02 -0800

P.s.,

I forgot to mention that if you do stick with the method that you are using,
you may be able to shorten your statement significantly by using the switch()
function.

Switch is kind of similar to a multipart iif(). It consist of a series of
expressions, and associated values. Switch will return the value
corresponding to the first expression that evaluates to true. Of course, the
value part of the expression can be a calculation, including nested functions
such as iif().

In the simplest case, if your from values and to values are adjacent, you
could just check the From amounts in descending order to find the first that
is less than price, such as:

Switch([Price]>[FromAmount11],iif([spec%11]>0,[Price]*[spec%11],[feeAmount11]),[Price]>[FromAmount10],iif([spec%10]>0,[Price]*[spec%10],[feeAmount10]),etc)

of course, that would have to be nested within your original iif() function
checking the four or five criteria that you want to be true. Also, if it is
not true that each range is adjacent, you would have to check the [ToAmount]
as well.

One other note regarding iif() and switch() functions, Access evaluates all
of the calculations in all of the expressions and value calculations, even if
the logic never gets to them. For example, looking at the switch function
above, you would think that if [Price]>[FromAmount11], Access would evaluate
the corresponding iif() function and that would be the end of it. But,
actually, Access would evaluate all of the expressions, and all of the
values, listed in the entire statement. Further, if any of those generated
an error, the expression would generate an error.

HTH, Ted Allen

"Ted Allen" wrote:

> Hi Mattias,
>
> I didn't go through all of the last statement that you posted in detail, but
> for sure the last iif() function has an error with the parenthesis. The
> following:
>
> IIf([Spec%4]>0;[Price]*[Spec%4]);[FeeAmount4])
>
> would look to Access to be missing the false part of the expression because
> it has a close ) after the true part.
>
> In general, it appears that your database may not be normalized, which is in
> turn making your task much harder than it has to be.
>
> You may want to look at whether you can normalize the database such that you
> have a related table with just a few fields such as:
>
> FKtoMain, FromAmount, ToAmount, SpecPerc, FeeAmount
>
> (Actually, you could even omit ToAmount if you always wanted to assume that
> the range would go to the next highest FromAmount if that is the case). In
> the list above, FKtoMain would store a Foreign Key to the primary key in the
> main table.
>
> Then, you would just enter as many records as necessary to define an
> unlimited number of price ranges and associated discounts or fees for each
> item.
>
> Storing the data in a more normalized fashion like this makes calculations
> much more simple, because you can easily use DLookup() or a correlated
> subquery to find applicable values.
>
> For instance, determining the applicable SpecPerc for the item would use a
> DLookup() such as:
>
> SpecPerc: DLookup("[SpecPerc]","YourTableName","FKtoMain = " & [YourPKField]
> & " AND [FromAmount] < " & [Price] & " AND [ToAmount] > " & [Price])
>
> Similarly, the FeeAmount would be:
>
> FeeAmount: DLookup("[FeeAmount]","YourTableName","FKtoMain = " &
> [YourPKField] & " AND [FromAmount] < " & [Price] & " AND [ToAmount] > " &
> [Price])
>
> (By the way, if you took the approach of eliminating the [ToAmount] field,
> you would adjust the [FromAmount] criteria such that it would be = to the Max
> FromAmount < [Price] - which you would use a DMax() for)
>
> Then, all you would have to do would be to check to see if SpecPerc is > 0,
> and use the appropriate expression.
>
> You could also use correlated subqueries to return the results above. I
> prefer to use them rather than DLookup() and the other Domain Aggregate
> functions, but sometimes the domain aggregate functions are easier to
> understand at first, which is why I used that method as an example.
>
> It would be easy to create the entries in the normalized table by just using
> a series of append queries to append all of the fields with the suffix 1 to
> the appropriate fields, then all fields with the suffix 2, etc from the main
> table.
>
> Short of normalizing the data, you may also want to consider creating custom
> functions to do these calcs for you. A short bit of visual basic code in a
> custom function could do the calc for you, which you could then call from any
> query, form or report just by using the custom function name and providing
> the necessary input parameters. The huge advantage of a custom function is
> that you only write the code in one place, so if it needs to be tweaked later
> on you can just change the function in one place, rather than having to go
> back and revise multiple confusing nested iif()'s.
>
> Hopefully this will help somewhat. If you would like any more info on a
> specific approach, or if I misinterpreted portions of your earlier posts, let
> me know and I can help further.
>
> -Ted Allen
>
>
> "Mattias" wrote:
>
> > Hi Bruce
> >
> > Thank you for getting back on this issue..I have renamed the below so you
> > can understand the purpuse of the IIf.
> >
> > If I try to save it like this in the query design I receive error message
> > "You have the wrong number of arguments"
> >
> > If at least one of the below 5 first conditions are No I want the whole
> > thing to be 0!
> > If they all are yes, I want to go on to the next IIf. For exampel the
> > Price=100 and the FromAmount1=1 and ToAmount1=25
> > The price is bigger than 1 but is not smaller than 25, then I want it to go
> > to the next IIf where FromAmount2=26 and ToAmount2=101, here the Price=100
> > finds its match check if the Spec%2>0 if this then the
> > CalculatedFee2a=Price*Spec%2, but if the Spec%2 is not >0 then the
> > CalculatedFee2a=the value given in FeeAmount2.
> >
> > CalculatedFee2a:IIf([SlagavgiftPåKöpare] And [Slagavgift1] And [Slagavgift2]
> > And [Slagavgift3] And [Slagavgift4]=yes;
> > IIf([Price]>[FromAmount1] And
> > [Price]<[ToAmount1];IIf([Spec%1]>0;[Price]*[Spec%1]);[FeeAmount1];
> > IIf([Price]>[FromAmount2] And
> > [Price]<[ToAmount2];IIf([Spec%2]>0;[Price]*[Spec%2]);[FeeAmount2];
> > IIf([Price]>[FromAmount3] And
> > [Price]<[ToAmount3];IIf([Spec%3]>0;[Price]*[Spec%3]);[FeeAmount3];
> > IIf([Price]>[FromAmount4] And
> > [Price]<[ToAmount4];IIf([Spec%4]>0;[Price]*[Spec%4]);[FeeAmount4]))));0)
> >
> >
> > Really thankful for any help on this
> >
> > Mattias
> > "BruceM" wrote:
> >
> > > What happens if they are all true (yes)? I will say that the result will be
> > > 100.
> > > IIf([SlagavgiftPåKöpare] = Yes And [Slagavgift1] = Yes And [Slagavgift2] =
> > > yes And [Slagavgift3] = Yes And [Slagavgift4] = Yes,100,0)
> > >
> > > Assuming this code is in a text box on a form, if all five fields are Yes
> > > (True) then the text box will show 100, otherwise it will show 0. Another
> > > IIf statement (if you want nested IIf statement) would go in place of the 0:
> > > ...Yes,100,IIf(...
> > > You could also substitute Or for And, and No for Yes. That will change it
> > > so that if any of the fields are No, the text box will show 0, otherwise you
> > > can do something else:
> > > IIf([SlagavgiftPåKöpare] = No Or [Slagavgift1] = No Or [Slagavgift2] = No Or
> > > [Slagavgift3] = No Or [Slagavgift4] = No,0,IIf(Field1] > [Field2],50,100))
> > > In this example, if any of the fields is No you will see 0 in the text box,
> > > otherwise you will check to seek if Field1 is greater than Field2. If it is,
> > > you will see 50 in the text box; otherwise you will see 0. If the first five
> > > fields are Yes AND Field1 is NOT greater than Field 2 you will see 100 in the
> > > text box.
> > >
> > > "Mattias" wrote:
> > >
> > > > Hi Bruce,
> > > >
> > > > The first part is yes/no fields all 4 and I want all to be true (yes) else
> > > > the result of the IIf should be 0.
> > > >
> > > > Mattias
> > > >
> > > > "BruceM" wrote:
> > > >
> > > > > "IIf([SlagavgiftPåKöpare] And [Slagavgift1] And [Slagavgift2] And
> > > > > [Slagavgift3] And [Slagavgift4]"
> > > > > For starters, what are you trying to do with this part of the statement?
> > > > > The IIf function is for evaluating a statement, returning one result if it is
> > > > > true, and another if it is false. The quoted part above is a list. There is
> > > > > nothing to evaluate. The statement can be neither true nor false.
> > > > >
> > > > > "Mattias" wrote:
> > > > >
> > > > > > Hi
> > > > > >
> > > > > > Thank you for your suggestions. I have shortened it down to 3 .
> > > > > > But now I have problems to make my IIf to work ok.
> > > > > > It looks like below now
> > > > > >
> > > > > > Mattias
> > > > > >
> > > > > > BeräknadSlagavgift2a:IIf([SlagavgiftPåKöpare] And [Slagavgift1] And
> > > > > > [Slagavgift2] And [Slagavgift3] And
> > > > > > [Slagavgift4];IIf([KlubbatBeloppSEK]>[FromBel1] And
> > > > > > [KlubbatBeloppSEK]<[TomBel1];IIf([Spec%1]>0;[Spec%1]*[KlubbatBeloppSEK]);[SpecBel1];IIf([KlubbatBeloppSEK]>[FromBel2]
> > > > > > And
> > > > > > [KlubbatBeloppSEK]<[TomBel2];IIf([Spec%2]>0;[Spec%2]*[KlubbatBeloppSEK]);[SpecBel2];IIf([KlubbatBeloppSEK]>[FromBel3]
> > > > > > And
> > > > > > [KlubbatBeloppSEK]<[TomBel3];IIf([Spec%3]>0;[Spec%3]*[KlubbatBeloppSEK]);[SpecBel3];IIf([KlubbatBeloppSEK]>[FromBel4]
> > > > > > And
> > > > > > [KlubbatBeloppSEK]<[TomBel4];IIf([Spec%4]>0;[Spec%4]*[KlubbatBeloppSEK]);[SpecBel4]))));0)
> > > > > >
> > > > > > "Mattias" wrote:
> > > > > >
> > > > > > > Hi
> > > > > > >
> > > > > > > I would like to ad the below to a query but I receive a errormessage about
> > > > > > > it being to large!!
> > > > > > >
> > > > > > > Thank you in advance
> > > > > > >
> > > > > > > Mattias
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
IIf([SlagavgiftPåKöpare]And[Slagavgift1]And[Slagavgift2]And[Slagavgift3]And[Slagavgift4];IIf([KlubbatBeloppSEK]>[FromBel1]And[KlubbatBeloppSEK]<[TomBel1];[Spec%1]>0;[Spec%1];[SpecBel1]);IIf([KlubbatBeloppSEK]>[FromBel2]And[KlubbatBeloppSEK]<[TomBel2];[Spec%2]>0;[Spec%2];[SpecBel2]);IIf([KlubbatBeloppSEK]>[FromBel3]And[KlubbatBeloppSEK]<[TomBel3];[Spec%3]>0;[Spec%3];[SpecBel3]);IIf([KlubbatBeloppSEK]>[FromBel4]And[KlubbatBeloppSEK]<[TomBel4];[Spec%4]>0;[Spec%4];[SpecBel4]);IIf([KlubbatBeloppSEK]>[FromBel5]And[KlubbatBeloppSEK]<[TomBel5];[Spec%5]>0;[Spec%5];[SpecBel5]);IIf([KlubbatBeloppSEK]>[FromBel6]And[KlubbatBeloppSEK]<[TomBel6];[Spec%6]>0;[Spec%6];[SpecBel6]);IIf([KlubbatBeloppSEK]>FromBel7]And[KlubbatBeloppSEK]<[TomBel7];[Spec%7]>0;[Spec%7];[SpecBel7]);IIf([KlubbatBeloppSEK]>[FromBel8]And[KlubbatBeloppSEK]<[TomBel8];[Spec%8]>0;[Spec%8];[SpecBel8]);IIf([KlubbatBeloppSEK]>[FromBel9]And[KlubbatBeloppSEK]<[TomBel9];[Spec%9]>0;[Spec%9];[SpecBel9]);IIf([KlubbatBeloppSEK]>[FromBel10]And[KlubbatBeloppSEK]<[Tom
Bel10];[Spec%10]>0;[Spec%10];[SpecBel10]);IIf([KlubbatBeloppSEK]>[FromBel11];[Spec%11]>0;[Spec%11];[SpecBel11]);0)

Relevant Pages

  • RE: How "large" can a IIf be in a query?
    ... for sure the last iif() function has an error with the parenthesis. ... You may want to look at whether you can normalize the database such that you ... the range would go to the next highest FromAmount if that is the case). ... custom function could do the calc for you, which you could then call from any ...
    (microsoft.public.access.queries)
  • RE: How "large" can a IIf be in a query?
    ... > Switch is kind of similar to a multipart iif(). ... >> You may want to look at whether you can normalize the database such that you ... >> custom function could do the calc for you, which you could then call from any ...
    (microsoft.public.access.queries)
  • FYI: IIf() oder Switch()?
    ... Dim res As Double ... x = Timer() ... IIf(): 6,714844 ... Switch(): 11,62109 ...
    (microsoft.public.de.access)
  • Re: Help with time
    ... Any MVP's that would like to help out with this "iif" clause I'd ... The Switch() ... and DayPart with these values. ... FROM yourtable INNER JOIN Dayparts ...
    (microsoft.public.access.queries)
  • Re: Nested IIF Functions
    ... I suppose there is still the advantage with Switch that the layout is more ... intuitive than the nested IIF but that might be less important if the ... IIF is a VBA ... call the VBA Switch function one time. ...
    (microsoft.public.access.queries)