Can Conditional Formatting do This

  • Thread starter Thread starter Gcook888
  • Start date Start date
G

Gcook888

I have a control [TotPct] and about 6-9 of these values is printed per group.
So a column of [TotPct] may look like:

0.33
0.55
0.77
0.22
0.11
0.15

I want to use conditional formatting to color the max of the group 0.77 to red.
Can anyone help w/this. I suspect it can be done with a good expression but I
don't see how. BTW Why doesn't expression builder start up from conditional
formatting?
 
I just tried it and yes, it can.

I set "field value equal to" then in the last box placed the equation

DMax("[Field1]","Table2")

Where Field1 would be the field of the textbox and Table2 would be the Record Source for
the report. Next, set the background or foreground color to whatever you want.
 
Wayne said
I just tried it and yes, it can.

I set "field value equal to" then in the last box placed the equation

DMax("[Field1]","Table2")

Where Field1 would be the field of the textbox and Table2 would be the Record
Source for
the report. Next, set the background or foreground color to whatever you
want.
Click to expand...

Thanks for considering my problem! My ctrl is actually unbound to a Field1 but
this puts me going forward
 
My ctrl [C] that I want to conditional format is unbound. It is = [A]/. A
is a field in the recordsource but the Control [A] is actually =Sum([A]) output
as a group footer. Ctrl is also a sum of field B which is calculated in
vba for certain conditions.

I set Field Value is equal to Max([A]/). The results:

[A] [C]
group 1
6 34 0.176
1 6 0.167
2 4 0.500
4 16 0.250
group 2
1 8 0.125*
12 12 1.000*
12 4 3.000
5 12 0.417

it picks wrong max and actually chooses 2 more not shown so it is picking 4. I
was surprised FIELD VALUE IS EQUAL TO worked somewhat. EXPRESSION IS doesn't
work
 
With these being unbound field, you are going to need some way to make the same
calculations and the textboxes where you can go through all of them in the background to
get the maximum before you start to display them.

I wonder if you could do this if you force the report to go through the OnFormat event a
2nd time and do the highlight in code in the OnFormat event of the section. The report
will go though the formatting twice if you place a textbox in the report footer with a
Control Source of =[Pages]. You would need a Static variable in the section's OnFormat
event and replace the value of this variable each time you find a larger number. Set a
form level "flag" variable in the report footer to let you know you've gotten that far.
In the section's format event you would check for the value of this variable. If it
indicates that you have been to the report footer you would then compare the value of the
textbox with the value of the variable remembering the maximum. If they match, set the
highlight of the textbox.

If you don't want the Pages textbox to show, you can try making its Visible property set
to No, if this still doesn't trigger going through the format twice, then just cover it
with something else or make it so small that it doesn't show.
 
Another possibility is a variation of the first. I just tried and DMax will accept an
equation. You should be able to place whatever equation you are using to calculate the
textbox into the DMax function.

Example:
DMax("[Field1] + [Field3]", "Table2")
 
Back
Top