IF THEN STATEMENTS

T

Tom

I have a query that updates a few fields based on data entered. I
need for one of the fields to have the following information
incorporated into it based on the outcome of the other fields. I need
for the statement to take the outcome and check to see where it falls
in the following criteria and then enter the results:

Less than 1 =400
1 but less than 2 =300
2 but less than 4 =250
4 but less than 6 =150
6 but less than 8 =125
8 but less than 10 =100
10 but less than 12 =92.5
12 but less than 15 =85
15 but less than 22.5 =70
22.5 but less than 30 =65
30 or greater =60

so if the number is 7.95, I need for it to enter 125.

Any help is greatly appreciated.

Thanks,

Tom
 
G

Guest

-----Original Message-----
I have a query that updates a few fields based on data entered. I
need for one of the fields to have the following information
incorporated into it based on the outcome of the other fields. I need
for the statement to take the outcome and check to see where it falls
in the following criteria and then enter the results:

Less than 1 =400
1 but less than 2 =300
2 but less than 4 =250
4 but less than 6 =150
6 but less than 8 =125
8 but less than 10 =100
10 but less than 12 =92.5
12 but less than 15 =85
15 but less than 22.5 =70
22.5 but less than 30 =65
30 or greater =60

so if the number is 7.95, I need for it to enter 125.

Any help is greatly appreciated.

Thanks,

Tom
.
Try this...probably the long way around to do it though
Click to expand...

if [fieldname]= 1 then
fieldname = 400
elseif [fieldname] >1 and [fieldname]<2 then
fieldname = 300
elsif etc etc for the other ones


some of the code I just used tonight..looks like the same
sort of thing, mine works ok

Private Sub NumberFranchise_AfterUpdate()
If NumberFranchise = 1 Then
DiscountFranchise = ""
ElseIf NumberFranchise >= 2 And NumberFranchise <= 4
Then
DiscountFranchise = 2.5
ElseIf NumberFranchise >= 5 And NumberFranchise <= 9
Then
DiscountFranchise = 7.5
ElseIf NumberFranchise >= 10 Then
DiscountFranchise = 10
End If
 
G

Graham R Seach

Tom,

Use the Switch function. I'm working on Access 2003 at the moment, but I
have a feeling it works in previous versions too).

SELECT
Switch(x<1,400,x<2,300,x<4,250,x<6,150,x<8,125,x<10,100,x<12,92.5,x<15,85,x<22.5,70,x<30,65,x>=30,60)
AS y, 7 AS x
FROM MyTable

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Top