Calculated Field

[Parra]

I have a field in a query called "Frequency" and the result is a number.

What I need is a calculated field that gets the result of "Frequency" and groups it into one of the following.

1
2
3-6
7-13
14-29
30 +

Almost like an IF functions, but I don't know if Access can do IF unctions. So if the result in Frequency is 10, then the calculated field would read 7-13.

Any suggestions, Thanks.

 

[Corticus]

Hi,

You can use IIF like Excel's If in an expression.

But I think you might need to do this through a UDF, like:

Function ranger(ByVal Numb As Integer)

Select Case Numb
    Case 1
        ranger = 1
    Case 2
        ranger = 2
    Case 3 To 6
        ranger = "3-6"
    Case 7 To 13
        ranger = "7-13"
    Case 14 To 29
        ranger = "14-29"
    Case Is >= 30
        ranger = "30+"
End Select

End Function

Insert this code in a standard module and use like any other function in Access, ie create query, add field you need to perform expression on, and in the next field in the query, put

Range: ranger([MyField])

HTH,

 

[Parra]

Hi Corticus,
I did as you wrote and added the code to a module. And added the calculated field next to the field I am looking up, but it did not work. I followed your post to the T.

Am I suppose to name the module any particular name and the field in the ranger([myrange]), is "[my range]" supposed to be re-named to Frequency.

Thanks

 

[Corticus]

ranger() is a new function. You use it like any other function in Access. If you would like to perform a calculation on a field, make a query with the table the field is in, create a new field in the query and type in this:

Range: ranger([Frequency])

You do not need to name the module anything special.

If you need more help, pm me you e-mail and I'll send you an example.

HTH,

 

[Parra]

Hi Corticus,

Thanks for your help. What I ended up doing was using an if function, but I forgot that in access you have to write it "IIF".

Thanks

 

[lmonaghan]

That is awesome!

This is about the coolest little trick I've seen in a while. That ranger function works great!