Conditional Formula

[Bill]

I am using Access 2000

I would like to create a multi-conditional (=IIF) type
statement for a rating system.

on form frmRates, I have following fields in tblRates plus
a weight field for the user to enter the actual weight in
pounds.

I have a table named tblRates
fields and values are:
m500 .15
m1000 .15
m1001 .14
m2000 .14
m2001 .135
m3000 .135
m3001 .12
m4000 .12
m4001 .115
m6000 .115
m6001 .10
m7500 .10
m7501 .0975

I need to compare -

if weight is between 500 and 1000 pounds .15 per pound
if weight is between 1001 and 2000 pounds .14 per pound
if weight is between 2001 and 3000 pounds .135 per pound
if weight is between 3001 and 4000 pounds .12 per pound
if weight is between 4001 and 6000 pounds .115 per pound
if weight is between 6001 and 7500 pounds .10 per pound
if weight is more than 7501 pounds .12 per pound
and a minimum charge is $75.00

I tried to do this through multi-nested iif and can't seem
to get this to work. Is there any easier method of doing
this?

Any help greatly appreciated.

 

[Support]

I am from the Access support department at http://www.enterpriseprojectconsulting.com and we would be happy to assist you in resolving your issue.

Thank you,

Enterprise Project Consulting

 

[Cheryl Fischer]

Bill,

I think the structure of your Rates table may be problematical, as it
appears to require two records to define the minimum and maximum weights for
a given rate. The first thing I would consider is to make the table look
like the following:

tblRates: Fields

RateDesc: Text
MinWeight: Number
MaxWeight: Number
Rate: Currency

The record for your .15 and .14 rates would look like the following:

RateDesc: M500
MinWeight: 500
MaxWeight: 1000
Rate: .15

RateDesc: M1001
MinWeight: 1001
MaxWeight: 2000
Rate: .14

Then, on your form you could put some code in the AfterUpdate event of the
Weight control which will lookup the applicable rate in tblRates:

Dim lngWeight As Long
lngWeight = Me!ShipWeight

Me!ShipRate = DLookup("[Rate]", "tblRates", lngWeight & " between
[MinWeight] and [MaxWeight]")


hth,