Defining The 3 Highest Values In A Series Of Fields

Hello Everyone,

I am in need of your vast array of knowledge and experience. I have been fighting with this Access report for a few days now, and I've finally decided to ask for your help.

I am running a report which provides a look at a set of 12 survey results. The 12 items are numerical in nature and are decimals with 2 decimal places. What I am trying to do is have Access automatically highlight the top 3 scores in one color, and the bottom 3 scores in another color.

So what I need is an expression that allows Access to discern the top 3 and bottom 3 from the series of 12 scores. in Excel you can identify the top score with this with the formula "Max(A1:A13)", but seeing as in Access we don't have the cell identifiers, this approach will not work.

I know that I will need to use conditional formatting in order to get the cells to highlight. I also see that there is "Max" and "Min" functions in Access as well, and I attempted to create and expression like this "=Max([field 1] AND [field 2] AND [field 3] AND, etc...)" with no luck. I also tried changing the "AND" to "OR", again without luck.

One thing that I should mention is that the report does contain a lot of other data, so I need to be able to apply whatever the best fix is, to only those 12 fields.

Unfortunately I am not accustomed to using code, and as a result I am unsure of how to input it properly. I am certainly open to code solutions, but I would also need a explanation of how to put it in properly.

The easiest solution for me, if possible, would be an expression that I can enter into the conditional formatting wizard to tell it to highlight the values.

If anyone has any ideas, I would appreciate it. I'll buy you a virtual beer :-)