C
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.
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.