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

[emckesso]

Hello,

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.


---------------------------------------------------

Private Sub Report_Load()
 
If Me![PercentCensus] < Me![Census3] Then
        If Me![PercentRevenue] < Me![Yellow3] Then
            Me![PercentRevenue].BackColor = vbRed
        Else
       If Me![PercentRevenue] < Me![Green3] Then
            Me![PercentRevenue].BackColor = vbYellow
        Else
            Me![PercentRevenue].BackColor = vbGreen
        End If
        End If
Else
If Me![PercentCensus] < Me![Census2] Then
        If Me![PercentRevenue] < Me![Yellow2] Then
            Me![PercentRevenue].BackColor = vbRed
        Else
       If Me![PercentRevenue] < Me![green2] Then
            Me![PercentRevenue].BackColor = vbYellow
        Else
            Me![PercentRevenue].BackColor = vbGreen
        End If
        End If
Else
If Me![PercentCensus] < Me![Census1] Then
       If Me![PercentRevenue] < Me![Yellow1] Then
        Me![PercentRevenue].BackColor = vbRed
        Else
       If Me![PercentRevenue] < Me![Green1] Then
            Me![PercentRevenue].BackColor = vbYellow
        Else
            Me![PercentRevenue].BackColor = vbGreen
        End If
        End If
Else
If Me![PercentCensus] >= Me![Census1] Then
       If Me![PercentRevenue] < Me![Yellow] Then
        Me![PercentRevenue].BackColor = vbRed
        Else
       If Me![PercentRevenue] < Me![Green] Then
            Me![PercentRevenue].BackColor = vbYellow
        Else
            Me![PercentRevenue].BackColor = vbGreen
        End If
        End If
End If
End If
End If
End If
 
End Sub
 
 

[TheSmileyCoder]

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?

[emckesso]

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_Setup:
[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_Report:
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.