If...Else...Then or Select

[Ezyflo]

I want to create a form where someone will provide some information and depending on what they put in - do a calculation.

Example: I have a combo box with different classifications. On the form they would select their classification and then input a value. The classification has a rate - if their value is $500,000 or less I use the rate of 1.00% if its over then I use 1.25%. I have about six different classifications and some of them - depending on the value has different rates.

Can anyone help me with the code for doing this? Any help would greatly be appreciated!!

 

[jebry]

Hi!

In general it sounds like you would use this setup:

Select Case YourComboBox.Value
Case = "1stClass"
If Value > 500,000 Then
Rate = 1.25
Else
Rate = 1.00
End If
Case = "2ndClass"
etc.
End Select

hth
Jeff Bridgham
bridgham@purdue.edu

 

[sdimov]

I would set up the following table:

ID CL_Def Limit Rate
01 Class1 0 1.250
02 Class1 500000 1.500
03 Class2 0 0.750
04 Class2 250000 0.850
05 Class2 350000 0.950
06 Class2 450000 1.050
07 Class3 0 0.500
08 Class3 50000 0.600
18 Class3 52000 0.620
09 Class3 60000 0.700
10 Class3 70000 0.800
11 Class4 0 0.250
12 Class4 200000 0.700
13 Class5 0 0.300
14 Class5 10000 0.400
15 Class6 0 0.325
16 Class6 1000 0.350
17 Class6 2000 0.425

and add to it if new rules arise.
i.e. each class starts with 0 and its min rate, then
you set the limits, if you add new value make sure it is logically placed in table
i.e for class4 you may add a limit of 100000 which rate must be between .25 and .7

Then execute the following query where you pass two params, Class Definition and Limit to return rate:

SQL:
SELECT TOP 1 Rate FROM Table1
WHERE CL_Def='Class3' AND Limit<=52000
ORDER BY Limit DESC;

Params could be read from a combo box (CL_Def) and a textbox for limit.

PS When you add new valuie to table, select CL_Def and Limit columns and execute SORT ASC to see how new value is placed where it belongs.

HTH,

Svet

 

[sdimov]

Little note on previous setup, it might be a bit slower than running IF or SELECT but you don't have to go and change VBA code when rates or limits change, all you have to do is make changes to table, you can even create a nice form to update table from.

Svet

 

[Ezyflo]

Jeff and Svet - Thanks for your input.

I'm new at this and don't have the faintest idea on how to go about it. I like the idea of creating a table with the information and rates, so that you don't have to change the code just the data.

What I'm trying to do is create a form that will be posted on a website where people will choose their &quot;Property Classification&quot; from a drop down list (combo box) and then input their property value. From there I want to be able to pull the rate from their classification selection, depending on what they input for their property value, and do a calculation.

i.e
Table data contains Property Classification of &quot;Residential Homestead&quot;, if property value is less than $500,000 then rate would be 1.00%, if property value is greater than $500,000 than rate would be 1.25%.

On the from the person chooses &quot;Residential Homestead&quot; from the drop down list and enters the [property value] of $300,000.

I need a calc button that they would click to do the calculation of
(([Property Value] * [Rate]) * [a fixed rate])

and display on the form in a text or label box.

I want to be able to make the form user friendly.

Its the stuff behind the scenes that I'm having trouble with.

Any help would greatly be appreciated! Thanks in advanced.

Dee

 

[sdimov]

I think you might want to look into ASP for what you would like to do and maybe some scripting. My suggestion was for Access. If you create the table and execute the query it will do exactly what you are describing.

Svet