Assign categories on the fly?

[sulu]

Hi,

I have a list of companies with employee counts, and I'd
like to assign a category level value based on the number
of employees.

So, for example, if the categories are:

"A" (0-99 employees)
"B" (100-499 employees)
"C" (500-999 employees)

and so on, I'd like a query expression to return "A" if
the value of the "number of employees" field is between 0
and 99, "B" if the value is between 100 and 499, and so on.

I've looked at IIF, but that doesn't seem to work for
anything with more than two categories. Am I mistaken?

Any help you can offer will be greatly appreciated.
Thanks.

 

[Jen]

Hi,

You could create a custom function and use the select case
(much cleaner than the IIf). Insert a new module into
your project and create a new function -- like so:

Function setCatCode(numEmployees as Integer) as String
' this function will return a string value based on the
' numEmployees argument

Select Case numEmployees
Case Is 0 to 99
setCatCode = "A"
Case Is 100 to 499
setCatCode = "B"
Case Is 500 to 999
setCatCode = "C"
Case Else
setCatCode = "N/A"
End Select
End Function

====================================================
Then, in your query, call this function like you would any
other - in the query grid you'd have an expression that
looks like:

Category_Code: setCatCode([Num_Employees])

where the [Num_Employees] is either a field in a table or
another expression in your query.

Hope that was clear.

Regards,
Jen

 

[sulu]

Jen,

Looks great!

Have a great holiday!

-----Original Message-----
Hi,

You could create a custom function and use the select case
(much cleaner than the IIf). Insert a new module into
your project and create a new function -- like so:

Function setCatCode(numEmployees as Integer) as String
' this function will return a string value based on the
' numEmployees argument

Select Case numEmployees
Case Is 0 to 99
setCatCode = "A"
Case Is 100 to 499
setCatCode = "B"
Case Is 500 to 999
setCatCode = "C"
Case Else
setCatCode = "N/A"
End Select
End Function

====================================================
Then, in your query, call this function like you would any
other - in the query grid you'd have an expression that
looks like:

Category_Code: setCatCode([Num_Employees])

where the [Num_Employees] is either a field in a table or
another expression in your query.

Hope that was clear.

Regards,
Jen

-----Original Message-----
Hi,

I have a list of companies with employee counts, and I'd
like to assign a category level value based on the number
of employees.

So, for example, if the categories are:

"A" (0-99 employees)
"B" (100-499 employees)
"C" (500-999 employees)

and so on, I'd like a query expression to return "A" if
the value of the "number of employees" field is between 0
and 99, "B" if the value is between 100 and 499, and so on.

I've looked at IIF, but that doesn't seem to work for
anything with more than two categories. Am I mistaken?

Any help you can offer will be greatly appreciated.
Thanks.
.

.