Substitute values in multiple controls. (1 Viewer)

[Neal]

I am working on a job evaluation db. I have a bunch of rating for which the values are drawn from a repeated scale.
I.e.
Rate this guy's work: Good Fair Bad
Rate this guy's punctuality: Good Fair Bad

I'm sure you get the picture.
I am collecting those values using an option group and storing numbers instead of "Good", "Fair", etc. That way I can average.
However, on my report, I want to return the text value rather than the number. I can do it with a nested Iif statement, but there are a couple of dozen controls that are set up the same way. What's the easiest way to get them all to return the text value?
Thanks
Neal

 

[Pat Hartman]

You can write a function to return the text value.

Public Function txtRating(RatingNum as Integer) As String
Select Case RatingNum
Case 1
txtRating = "Bad"
Case 2
txtRating = "Fair"
Case 3
txtRating = "Good"
Case Other
txtRating = "Unknown"
End Select
End Function

Then use the function in your query or report controls by passing it the field you want to convert.

In a query:
Select txtRating(WorkRating) AS txtWorkRating,....

or

In a report control:
=txtRating(WorkRating)

 

[Neal]

Works great, Pat.
Thanks.