Access arithmetic Expressions

[Lennon Gilbert]

I use ACCESS 97, a single table, and bill on the report
format for these 2 rates. The expression =IIF([Text25]
=<2,7.6,([Text25]-2)*2.1+7.6) worked well for this 2 stage
rate. Text25 is the Water usage/1000 gals. New rate is:
0 - 2 (1000 gals.) @ $1.63/10OO + $3.65 fixed fee
3 - 10 " @ $1.83 " + $4.45 "
11 - 15 " @ $2.23 " + $4.45 "
16 - Up " @ $2.73 " + $4.45 "
I tried this: ((=IIF[Text25]=<2,([Text25]*1.63)+3.65)+
(=IIF[Text25]=>3,=<10,([Text25]*1.83)+4.45)+(=IIF[Text25]
=>11,=<15,([Text25]*2.23)+4.45)+(=IIF[Text25]=>16,([Text25]
*2.73)+4.45)) It don't work for this 4 stage pricing
structure. What am I doing wrong?
Lennon 10/26/03

 

[Gary Miller]

Lennon,

You are now in need of a LookUp table to hold your rates and
then calculate the amount from the table. Beauty is, you
just change the paramters and amounts when needed.

tblRates
LowUsage High Usage Rate
0 2000 1.83
3000 10000 2.23

Now you can use DLookup to find the rate that you need.
There are better methods that DLookup, but it is easy and
sufficient for most applications. Set your control to be...

= [Text25] * DLookup("Rate", "tblRates", "[Text25] >=
[LowUsage] AND [Text25] <= [HighUsage]"

BTW - Do yourself a favor and rename Text25, and similarly
name controls, to something meaningful before you get to far
along. How about txtUsage or Usage? It can get very
confusing down the road.

Gary Miller
Sisters, OR

I use ACCESS 97, a single table, and bill on the report
format for these 2 rates. The expression =IIF([Text25]
=<2,7.6,([Text25]-2)*2.1+7.6) worked well for this 2 stage
rate. Text25 is the Water usage/1000 gals. New rate is:
0 - 2 (1000 gals.) @ $1.63/10OO + $3.65 fixed fee
3 - 10 " @ $1.83 " + $4.45 "
11 - 15 " @ $2.23 " + $4.45 "
16 - Up " @ $2.73 " + $4.45 "
I tried this: ((=IIF[Text25]=<2,([Text25]*1.63)+3.65)+
(=IIF[Text25]=>3,=<10,([Text25]*1.83)+4.45)+(=IIF[Text25]