473,836 Members | 1,388 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Conditional Formatting Using If/Then/Else in Report Detail Section

2 New Member

I need to create conditional formatting of a text box in the detail section of a report. The formatting is based on multiple parameters that are stored in a query. For example, pretend that [Census3] = 50%, [Yellow] = 80%, and [Green] = 90%. I will check [PercentCensus] in the report against [Census3] (from the query, but currently stored in a text box on the report via a Dlookup - textbox name is still [Census3]). If [PercentCensus] is less than [Census3] then I want to compare the value of [PercentRevenue] (another report text box) against [Yellow3] and [Green3] to find the appropriate background color. In this case, [Yellow3]=[Yellow]*[Census3] - or 40%.

if [PercentCensus] is > than [Census3] I will compare against [Census2], and so on.

Basically, I am reducing the threshold that my [PercentRevenue] needs to meet in order to be yellow or green if the [PercentCensus] value is below a certain threshold.

Below is the code I currently have. I am using the same code for the Report Footer (just different text box names) section and it works perfectly. Being that this is a report, the number of rows in the detail section always changes based on the filters used to run it. I'm finding that with this code, it always uses the lowest value in the detail section when comparing against the thresholds.

Is there a better way to conditionally format the detail section when you have multiple layers of criteria to meet. Any help would be much appreciated.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Load()
  3. If Me![PercentCensus] < Me![Census3] Then
  4.         If Me![PercentRevenue] < Me![Yellow3] Then
  5.             Me![PercentRevenue].BackColor = vbRed
  6.         Else
  7.        If Me![PercentRevenue] < Me![Green3] Then
  8.             Me![PercentRevenue].BackColor = vbYellow
  9.         Else
  10.             Me![PercentRevenue].BackColor = vbGreen
  11.         End If
  12.         End If
  13. Else
  14. If Me![PercentCensus] < Me![Census2] Then
  15.         If Me![PercentRevenue] < Me![Yellow2] Then
  16.             Me![PercentRevenue].BackColor = vbRed
  17.         Else
  18.        If Me![PercentRevenue] < Me![green2] Then
  19.             Me![PercentRevenue].BackColor = vbYellow
  20.         Else
  21.             Me![PercentRevenue].BackColor = vbGreen
  22.         End If
  23.         End If
  24. Else
  25. If Me![PercentCensus] < Me![Census1] Then
  26.        If Me![PercentRevenue] < Me![Yellow1] Then
  27.         Me![PercentRevenue].BackColor = vbRed
  28.         Else
  29.        If Me![PercentRevenue] < Me![Green1] Then
  30.             Me![PercentRevenue].BackColor = vbYellow
  31.         Else
  32.             Me![PercentRevenue].BackColor = vbGreen
  33.         End If
  34.         End If
  35. Else
  36. If Me![PercentCensus] >= Me![Census1] Then
  37.        If Me![PercentRevenue] < Me![Yellow] Then
  38.         Me![PercentRevenue].BackColor = vbRed
  39.         Else
  40.        If Me![PercentRevenue] < Me![Green] Then
  41.             Me![PercentRevenue].BackColor = vbYellow
  42.         Else
  43.             Me![PercentRevenue].BackColor = vbGreen
  44.         End If
  45.         End If
  46. End If
  47. End If
  48. End If
  49. End If
  51. End Sub
Sep 23 '10 #1
2 4453
2,322 Recognized Expert Moderator Top Contributor
What you have displayed formats the entire report. Is taht what you want? Or do you want "per detail" formatting?

Are you criteria stored in a table? If so how does that table look? If not how is the details of the different Percentage threshold stored?
Sep 24 '10 #2
2 New Member
I need it to be for each line of detail - I can't figure that part out.

My criteria are stored in textboxes on the report (with visible=no). The texboxes are Dlookups to a query, which pulls from a table but adds some calculations.

Table Fields
tbl_Project_Set up:
[Revenue % Yellow Threshold]
[Revenue % Green Threshold]
[Census % Normal Rev Threshold]
[Census % Reduced Rev Threshold Level One]
[Census % Reduced Rev Threshold Level Two]

Query Feilds-
qsel_Thresholds _for_Summary_Re port:
Yellow: Revenue % Yellow Threshold
Green: Revenue % Green Threshold
Census1: Census % Normal Rev Threshold
Census2: Census % Reduced Rev Threshold Level One
Census3: Census % Reduced Rev Threshold Level Two
Yellow1: [Revenue % Yellow Threshold]*[Census % Normal Rev Threshold]
Yellow2: [Revenue % Yellow Threshold]*[Census % Reduced Rev Threshold Level One]
Yellow3: [Revenue % Yellow Threshold]*[Census % Reduced Rev Threshold Level Two]
Green1: [Revenue % Green Threshold]*[Census % Normal Rev Threshold]
Green2: [Revenue % Green Threshold]*[Census % Reduced Rev Threshold Level One]
Green3: [Revenue % Green Threshold]*[Census % Reduced Rev Threshold Level Two]

My Dlookups on the report are straight forward, for example: =DLookUp("[Yellow]","qsel_Thresho lds_for_Summary _Report","[ID]=[SetupID]")

[SetupID] is a constant set to 1 - so it finds the record in the query with ID = 1. I could have done the Dlookups in VBA (and did actually try it), but it's just messier to me.
Sep 24 '10 #3

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

Similar topics

by: Alex | last post by:
Is it possible to set conditional formatting using code in a module? Thanks for your help Alex
by: John | last post by:
My report is based off of a table. I need a record on the report to be highlighted if that record ID is in a different table. Something like, if record ID exists in Query X, then conditional format this entry on the report. Any suggestions?
by: Melissa | last post by:
What specifically causes the Format event of a report's section to fire? Thanks! Melissa
by: Von Bailey | last post by:
I have a form where the conditional formatting is set on some fields to bold if certain conditions are met. However, when the conditions are met some of the data that is to bold is either not showing or only part of the data in the field displays (i.e. it will display the last name but not the first name or it will display neither). When the conditional formatting is removed it displays all the data as it should be display so the data...
by: Sara | last post by:
The problem: Conditional formatting bold, red when field Value < date() sets the field background to white - always - whether condition is met or not. I want the field unfilled and just red/bold when the date has passed. Background: I have 2 related fields that are only rarely active, so I manage them in the query for the subform: ShowBlanket: IIf(,"Blanket","") and
by: Andrew Chanter | last post by:
Does anyone know a way you can use conditional formatting to create a banded style view as is commonly seen on the internet. (In othe words the first record appears on a gray background, the 2nd on white, the third on gray etc etc.) I could write an expression based on the odd/even status of the primary key but this wont work when sorting and filtering is applied. Any suggestions gratefully received.
by: Tim Marshall | last post by:
Access 2003. A report of mine was hanging on print preview. It turned out the issue was a bound text box where the data was too big to fit all on one page. When I changed the "keep together" property for the detail section to NO, the report previewed and performed as expected. However, I'm now faced with a minor irritant I'd like to be able to deal with. Some other records with far less content in the text box at issue end up getting...
by: sara | last post by:
Hi - Is it possible to hide the detail section of a report at run time? I have a report that prints all details, with summary lines. The user would like the report ALSO with just summary lines. It seems the simplest thing is to run the code (see below) once with the detail section showing, and a second time hiding the detail section. I can't figure out the code to do that and don't see it posted.
by: NJonge01 | last post by:
Hi, I'd like to produce a report that basically looks like an excel printout. Description: Profile Print Report Detail Section with 3 bordered text boxes all text boxes are 'abutted' against each other (where one ends, the next begins) all text boxes have the 'can grow' property set to yes. Detail Section with 'can grown' = yes
by: Trish | last post by:
Hello All and thanks in advance for your help. The code below works just fine in the Print event of the report Detail section. Is it possible to condense it using a loop or something? Can you use variables when setting attributes? Thanks again. I learn a lot from reading answers to other people's posts, but haven't seen anything that simplifies this issue. Tricia
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.