I have a form in which a calculation must be performed. I have a table called tbl_Rates. On this table I have the following information.

Start End Rate
0 199.99 1%
200 399.99 1.5%
400 499.99 2%
500 1,000,000 3%

On the form the user will enter a dollar amount and depending on the amount the resulting field needs to calculate the percentage of the amount. I need the code that will calculate the amount. In other words if I have an amount of $350.00 I need the calculation to look at the table find the percentage rate and then calculate the result.

Can someone help?

Thanks
Michael

Let's say the dollar amount is typed in text box txtAmount. Then,

DLookup("Rate", "tbl_Rates", "Start < " & txtAmount.Value & " And End > " & txtAmount.Value)

should return the rate (or Null if nothing found--remember, even with a large End value such as 1,000,000, someone may still exceed it or they may enter a non-numeric or negative number which could result in an error, so you should have some error checking or trapping). At least, if I'm understanding you right--that is, you type in an amount, and you want the rate where the typed in amount falls in between Start and End, right?

Hope that helps!

My first question is this:

Is the Rate information stored as a number (e.g., 0.015) or as a text string (e.g., "1.5 %").

I'm going to proceed as if it is stored as a number, and you enter the amount into a text box called "Ammount", and the result will be stored in a text box called "AdjustedAmmount". You could probably do what you need to with a record set. After you enter the ammount into the form, have some button with the following code in its "OnClick" event:


Dim strSql as string

Dim RateValue as Long

strSQL = "SELECT * FROM tblRates ORDER BY Start"

set rstRates = CurrentDB.OpenRecordset(strSQL)

rstRates.MoveFirst

Do

If rstRates.Fields("Start") < Me.Ammount AND rstRates.Fields("End") > Me.Ammount Then

RateValue = rstRates.Fields("Rate")

End If

Loop While Not (rstRates.EOF)

Me.AdjustedAmmount = (Me.Ammount * RateValue)

FYI - Babrandt's solution is much simpler than mine, so I would use his.

Thanks Guys baBrandt's worked great!

Michael