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 said:
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
:
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.
:
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
:
"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.
:
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)
:
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
