iif trouble gets me headaches

[Robbie1974]

I'm having trouble with some calculations on my form.

It's a form for making quotes.
All the articles in the database have quantity discounts and there is sometimes an extra customer discount on top of that.

For example: if a customer buys 1 pack of paper he pays $4.36
if he buys 10 packs he pays $3.92 (10%) and if he buys 50 he pays $3.49(20%)

The discount are located in the listbox where I lookup my articles. When I enter the above example it gives me 10% startting from 2 packs instead of 10 and from 6 packs it gives me 20% instead of 50 packs.

Is there anyone that can help?

This is my iif code:

=IIf([Forms]![frmOfferte]![toptop]=Waar And [Referentie].column(2)="N";round([prijs]*(1-[Referentie].column(6)/100)*(1-10/100);2);IIf([qty]>=[Referentie].column(5);round(([prijs]*(1-[Referentie].column(6)/100))*(1-[Korting]/100);2);IIf([qty]>=[Referentie].column(3);round(([prijs]*(1-[Referentie].column(4)/100))*(1-[Korting]/100);2);IIf([prijs]=[prijs];round([prijs]*(1-[Korting]/100);2);round([prijs];2)))))

toptop = highest discount minus 10%
korting = extra customer discount
[Referentie].column(2) = discount allowed y/n
[Referentie].column(5) = highest quantity (50)
[Referentie].column(6) = highest discount(20)
[Referentie].column(3) = lowest quantity (10)
[Referentie].column(4) = lowest quantity (10)

Thanks for suggestions.

 

[wazz]

you might want to use several ElseIf clauses.

 

[stopher]

Your probably better off using the switch function instead of nested if's as it is much easier to see what you are doing. I've converted your statement as follows:

Switch (
[Forms]![frmOfferte]![toptop]=Waar And [Referentie].column(2)="N", 
round([prijs]*(1-[Referentie].column(6)/100)*(1-10/100);2),

[qty]>=[Referentie].column(5),
round(([prijs]*(1-[Referentie].column(6)/100))*(1-[Korting]/100);2),

[qty]>=[Referentie].column(3),
round(([prijs]*(1-[Referentie].column(4)/100))*(1-[Korting]/100);2),

[prijs]=[prijs],
round([prijs]*(1-[Korting]/100);2),

1=1,
round([prijs];2)

)

Note that you must submit pairs of arguements and they are assessed from left to right. For the last pair I've put a default 1=1 so that if non of the other criteria are met, this one will apply.

You have a criteria [prijs]=[prijs]. Isn't this always going to be true ?? What is the purpose ?

I can't really see any problem with your original logic but it's a bit messy to check carefully. Maybe if you use switch it might fall into place.

Run the formula in a query and add the component part to the query also. Then you can see if the right inputs are bing use.

Personally I wouldn't implement this range thing like this. I would have the Referentie table as:
Min___Max___discount
0______9_____0.0
10_____49____0.10
50____9999___0.20

Then create a cartesian join query and use a between where clause to pull the right values. The benefit is if you decide to add more discount ranges it's simply another row in a table rather than having to add columns to tables and recode queries. There's an example here

hth
Stopher

 

[Robbie1974]

Problem solved

Thanks