By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

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

P: 2

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
Share this Question
Share on Google+
2 Replies

Expert Mod 100+
P: 2,321
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

P: 2
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
[Revenue % Yellow Threshold]
[Revenue % Green Threshold]
[Census % Normal Rev Threshold]
[Census % Reduced Rev Threshold Level One]
[Census % Reduced Rev Threshold Level Two]

Query Feilds-
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_Thresholds_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

Post your reply

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