473,322 Members | 1,538 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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
Nov 10 '09 #1
6 2320
Megalog
378 Expert 256MB
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.
Nov 10 '09 #2
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?
Nov 10 '09 #3
Megalog
378 Expert 256MB
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.
Nov 10 '09 #4
Megalog
378 Expert 256MB
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)
Nov 10 '09 #5
mshmyob
904 Expert 512MB
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,
Nov 10 '09 #6
mshmyob,

Thanks again!

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

-Adam
Nov 10 '09 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

10
by: Richard | last post by:
The style sheet shown below is from the suckerfish vertical menu. http://www.htmldog.com/articles/suckerfish/dropdowns/example/vertical.html I've added in a few minor changes to color code the...
2
by: RWD | last post by:
I am trying to figure out how to change the target frame in my hyperlink on a DHTML menu. The menu is in one frame and the target frame is called "main" The code is below: Thanks in advance...
7
by: Keith Smith | last post by:
How can I change the background IMAGE (not just color) of a CELL in a table? I know that I can do this using CSS, but I really need to be able to do it using JavaScript. Anyone know how? Must...
3
by: Peter Williams | last post by:
Hi All, I want to write some javascript for a html page which does the following. Imagine that the page contains a table with 2 columns and 3 rows, e.g.: +---+---+ | A | B | +---+---+
4
by: TJS | last post by:
can the rows and columns of a textarea element with an id be changed on the fly ? if so is there an example ?
0
by: RSH | last post by:
I am working with the DataGridView control in a WinForm. I have written the code to change the background color of a cell after it is edited. I also change the color of the row (a different...
4
by: HB | last post by:
So I have a table in ASP.NET <table runat="server" id="tblMyTable"> <tr> <td id="cellMyCell">word</td> </tr> </table> How do I reference the cell "cellMyCell" from the Vb.NET 1.1...
11
by: free4trample | last post by:
First of all let me say that I know very little about javascript. what i need to do is to write a javascript functin which will change the background color of the table rows based on entrees in...
6
by: happyse27 | last post by:
Hi All, How do I change the height of the main menu as the height is inconsistent due to some of main menu's item stretches to 2 rows while, the other main menu's items are stretched to 1 row. ...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.