Look up value and return a number

[jmhicsupt]

I need to write a hugh IF statement.

If [Title] like "Sales*", and If Salary <$20,000 then value of field [Level]=1
or
If [Title] like "Sales*", and If Salary Between $20,001 and $40,000 then value of field [Level]=2
or
If [Title] like "Data*", and If Salary <$20,000 then value of field [Level]=1
If [Title] like "Data*", and If Salary <$20,000 then value of field [Level]=2
and so on

Is there an easy way to do this?

 

[PHV]

I need to write a hugh IF statement
In which language, VBA, SQL ... ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jmhicsupt]

In VBA

 

[jmhicsupt]

Is there a way to do this in a query?

 

[PHV]

Something like this ?
If [Title] Like "Sales*" Or [Title] Like "Data*" Then
If Salary <= 20000 Then
[Level] = 1
ElseIf Salary <= 40000 Then
[Level] = 2
End If
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jmhicsupt]

How would I put this in a query?

 

[PHV]

So, you wanted SQL !
SELECT ...
, IIf([Title] Like 'Sales*' Or [Title] Like 'Data*',IIf([Salary]<=20000,1,IIf([Salary]<=40000,2,0)),0) AS Level
FROM ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jmhicsupt]

Almost there.

How do I do BETWEEN.

So if Salary Between 20000 and 30000

 

[PHV]

Can you please formulate the whole new specs ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jmhicsupt]

I figured out how to do the BETWEEN, but now I have run into another problem. The string is too long. I think I have to go another route. Can I put the ranges in a table and have the query look up the ranges and put the applicable range in the field? If so, I will post the fields.

 

[PHV]

Yes, having the ranges in a table is a better method: easier to modify the rules.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jmhicsupt]

Thanks for the response. I do need to put this in VBA code. How do I declare the variables? I don't have a clue how to do this. I pasted this in

If [Title] Like "Sales*" Or [Title] Like "Data*" Then
If Salary <= 20000 Then
[Level] = 1
ElseIf Salary <= 40000 Then
[Level] = 2
End If
End If

But it comes up with error - variables not defined.

What do I have to type before I get to the If statement?

Thanks in advance.