How do you change the background color of rows in top 25% of results?

[AdamOnAccess]

Hi all,

I'm in Windows XP, Access 2007.

I need to create either a tabular subform or a list box that, from a query, returns a list of records showing amounts of money sorted in descending order. The top 25% of should have a background color or red; the next 25% should have a background color of yellow; the next 25% should have a background color of light blue; and the last 25% should have a background color of white.

I understand most of what I need to know to do this, but I don't know how to change the color of rows in a subform or list box, based on percentiles.

Has anyone seen any code like this?

-Thanks,
Adam

[Megalog]

If you can use a subform in datasheet view, then I would use conditional formatting.

Set 3 conditions across all the fields to read:

Expression is [Percent]>0.75 (Set to red fill color)
Expression is [Percent]>0.5 And [Percent]<0.75 (set to yellow fill color)
Expression is [Percent]>0.25 And [Percent]<0.5 (set to light blue fill color)

"[Percent]" is whatever the field name is that you have formatted for percent values.

If neither of the 3 conditions are met, the fields will simply be the default background color, which would be white.

This works good in your case, since you can only have 3 conditional formats total. If you ever need to add a 4th case, then this would have to be done in vba somehow.

Personally, unless this needs to be in a form, I would do this formatting in a report instead.

[AdamOnAccess]

Hi Megalog,

Thanks for the response. I'm afraid this will need to be a form because once the user learns which records are at the top 25%, then they will need to be able to click and drill down into the records for more details.

I looked into the conditional formating. I have never used it before. It appears pretty straight forward. The only thing that throws me is this: When you are working on the detail section of a tabular form in design view, you only have 1 row of fields. I thought that once you set properties for that row, you can't alter the properties of separate rows during display.

Will conditional formating work in a tabular form for the detail records?

[Megalog]

Typically any properties you set to those fields will be reflected across all the records shown in a datasheet view. Conditional formatting is different though, and will separately format each record.

[Megalog]

Also, a small modification to the conditions I posted above... You should use a greater than or equal (>=), instead of greater than (>). Otherwise, .75, .5, and .25 wont trigger conditions. So, use this instead:

Expression is [Percent]>=0.75 (Set to red fill color)
Expression is [Percent]>=0.5 And [Percent]<0.75 (set to yellow fill color)
Expression is [Percent]>=0.25 And [Percent]<0.5 (set to light blue fill color)

[mshmyob]

Hello Adam.

Check out this demo I did that may help you. It uses continuous forms (tabular) and does what you are looking for with no limitations on the number of conditionals.

cheers,

[AdamOnAccess]

mshmyob,

Thanks again!

I'm almost up to that part of the application. I'll check out the sample when I start.

-Adam