Choose function does not appear to be rounding the index

[cjfazio]

I have a text field in a report named NumberRating and I want it to provide
the average rating of all the records displayed. If I use the command
=Avg([NumberRating]) in the Control Source and set the Format = Fixed and
Decimal Places = 0, I get the appropriate results. For example a 4 record
set of 1, 2, 2, 2 would result in an average rating of 2 (1.75 rounded to the
nearest whole number).

But I really want the rating to display a text such that Green = 1, White =
2, Yellow = 3, Red = 4. To do this I used
=Choose(Avg_([NumberRating]),"Green","White","Yellow","Red"), as the Control
Source. However, for the example given above I now get a value of Green
(1.75 rounded down to 1). This does not make sense because Access Help
states for the Choose function that if the Index is not a whole number, it is
rounded to the nearest whole number before being evaluated.

I thought that maybe to make it work I could first take the average of the
NumberRating and then use the Choose function but I don't know how to create
two distinct expressions in the Control Source for this text field.

 

[cjfazio]

Thanks Chris. It worked perfectly.

It's not rounding. It's truncating the number. Use this expression (all one
line):

CHOOSE (ROUND(AVG(NumberRating), 0), "Green", "White", "Yellow", "Red")

Chris
Microsoft MVP

I have a text field in a report named NumberRating and I want it to provide
the average rating of all the records displayed. If I use the command
=Avg([NumberRating]) in the Control Source and set the Format = Fixed and
Decimal Places = 0, I get the appropriate results. For example a 4 record
set of 1, 2, 2, 2 would result in an average rating of 2 (1.75 rounded to the
nearest whole number).

But I really want the rating to display a text such that Green = 1, White =
2, Yellow = 3, Red = 4. To do this I used
=Choose(Avg_([NumberRating]),"Green","White","Yellow","Red"), as the Control
Source. However, for the example given above I now get a value of Green
(1.75 rounded down to 1). This does not make sense because Access Help
states for the Choose function that if the Index is not a whole number, it is
rounded to the nearest whole number before being evaluated.

I thought that maybe to make it work I could first take the average of the
NumberRating and then use the Choose function but I don't know how to create
two distinct expressions in the Control Source for this text field.