Converting Text to Numeric Value

[cjfazio]

I have a form where people enter data on a certain subject and then select a
rating from a combo box (either green, white, yellow or red). The combo
box obtains its values from table with two fields (1) rating and (2) numeric
value. The rating values are the text values shown above, the numeric values
are (1,2,3,4).

I created a query that joins the rating table with the data table. Now I am
trying to create a report that will display the average rating for selection
of records. I can get the report to display the numeric average (i.e. 2.25)
of all the records but have not figured out how that number can be converted
back to the text rating (i.e. white).

Can anyone help me get started.

 

[Tom van Stiphout]

On Thu, 15 May 2008 20:19:00 -0700, cjfazio

Check out the Choose function in the help file. You may be able to
write a sql statement like this:
select choose(int(avgRating), "green", "white", "yellow", "red")
from SomeQuery

-Tom.

 

[cjfazio]

Thanks Tom, using the Choose function looks like the right way to go but I
am having a problem with it rounded to the nearest whole value.

For my report, I create a text field that displays the numeric equivalent
value of my rating system (the table is setup such that Green = 1, White = 2,
Yellow = 3 and Red =4).

Initially I used the command =Avg([NumberRating]) in the Control Source,
set the Format = Fixed and Decimal Places = 0. So for example a report
yielding 4 ratings of white, white, white and green will return a 2 (rounding
up the value of 1.75). However, when I use the command
=Choose(Avg_([NumberRating]),"Green","White","Yellow","Red"), as the Control
Source, using the same example above, I get an average value of green (1.75
rounded down).

Is there any way to use the Choose function and round to the nearest value