Need Help useing IF Functions in Queries 2

[Dixen]

I have a field that I want to calulate a number of points off of based on the value. Basicly if the starting value is between 21 and 31 assign a code of 1, if between 31 and 41 assign a value of 2, etc... However, when I write the IF statement in the query design view I get an "undefined Function 'IF' in expression" error. Can anyone point me in the right direction here?

 

[WildHare]

Unless you typo'ed in your post , you're missing an I - the function is IIF (ImmediateIf)

Depending on how many calcs you need to do, it may be more efficient to build a quick table and put your values and decodes there and refer to it from your main records.

"Remember, you're unique - just like everyone else"
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[Dixen]

Yes, Typo... Sorry about that.

What I need to do quite simply is this:

<21 = 0 value
=21 but <31 = 1
=31 but <41 = 2
=41 but <51 = 3
=51 but <71 = 4
=>71 = 5

Thanks for your help.

 

[tlbroadbent]

In a query, use IIf rather than If. You can nest the function calls.

Select StartVal,
IIf(StartVal Between 21 And 30, 1, StartVal Between 31 And 40, 2, ...) As Code
From table_name

However, if you have a large number of ranges to test, I recommend writing a VBA function to determine the Code in order to reduce the query complexity. In VBA you can use the IF... Then ... Else statements. You canalso use the SWITCH function.

Public Function fnGetCode(sValue As Integer) As Int

If sValue >= 21 An sValue < 31 Then
fnGetCode=1
ElseIf sValue >= 31 An sValue < 41 Then
fnGetCode=2
Else ... etc.
End If

In the query you evoke the function like this.

Select StartVal,
fnGetCode(StartVal) As Code
From table_name Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[WildHare]

Another quick way to do this in a function is with a Select...Case statement BUT you must start with the HIGHEST value and work down:

Select Case sValue

Case => 71 GetNum = 5
Case => 51 GetNum = 4
Case => 41 GetNum = 3
et cetera...

End Select

The reason you MUST start with the maximum evaluation and work down is because as soon as Select..Case gets a TRUE, it exits.




&quot;Remember, you're unique - just like everyone else&quot;
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com