Conditional Formatting Access 2007 - Reports

[liztowne]

I'm putting together a report (a very simple one) where I'm grouping by crime type (all, violent, property), displaying city and district data with percent change. I need to format the field background color based on crime type (i.e. pale red for violent, pale green for property, pale purple for all) and also want to format the text in the percent change field to be red if positive (crime went up) or blue if negative (crime went down).

I can do either or through the conditional formatting button, but I can't figure out how to do both. I'm hoping I'm missing the obvious as I'm not an access guru (I'm a statistican - making things pretty is not my usual bag, but someone handed me an excel report with the conditional formatting and asked me to automate it). thanks in advance for any and all help!

[NeoPa]

I don't get the problem Liz. One control has one setup using Conditional Formatting, and the other control has a separate one that also uses Conditional Formatting. Where's the problem?

[liztowne]

Sorry for not being clear - I'm formatting all the fields in the report row to have the same background color based on crime type and want the font in the 28d % field to also format based on being negative or positive.

Crime Area 28d %
All City -12.3%
Violent City 8.2%
Property City -18.4%

So the above would be purple across the first row, with -12.3% in blue font, pale red on the second row with 8.2% in red font and green on the last row with -18.4% in blue font.

If there were 6 options for the conditional formatting section it would be fine. I'm fairly sure I need to code it, but I can't figure out where/how. I'm decent in programming in other languages/packages, but just don't have much familiarity with access/sql/vba.

[NeoPa]

I'm afraid that three options is the limit with Conditional Formatting Liz :-(

If you need to apply logic to differentiate between six separate conditions then I'm afraid even good coding skills won't help, as the scope just isn't there.

[Stewart Ross]

@liztowne, what version of Access are you using? A2007/2010 can apply more than three conditions when using conditional formatting, but A2003/2000 cannot as NeoPa said.

If you are unable to use conditional formatting to achieve the effect you want, it should also be possible to change the font colour for a control dynamically in VBA code in response to the On Format event of the report's Detail section.

If you take the VBA approach to working with font colours outside of the ones for which pre-defined constants are available (which I think will be the case for the colour shades you are mentioning) you would need to note down the numeric values of the colours concerned before you start to write the VBA code. The numeric values are shown in the property settings for the control concerned after you apply a colour change in design view, say.

One other approach you may want to consider is to overlay two textbox controls, the one on top being the control for the text itself which you can conditionally format and the other being an unbound control that you can set the background colour of using a separate conditional format. With one overlaid on the other you may be able to achieve the combined effect you are mentioning with no VBA programming at all. The top control would need to be set to have transparent background, of course, to allow the fill from the one below to show through.

-Stewart

[NeoPa]

Very interesting Stewart. Are you saying that there is actually a way to effect the properties of controls on continuous forms/reports if you apply the changes in the Format event procedure?

In such circumstances, would there be any exposure to a user saving the form if they're allowed to (Many do, as you know, by pressing Ctrl-S when actually intending to save the current record.)?

[Stewart Ross]

@NeoPa, the attached PDF files show the two techniques in use together. This is a sample based on the Catalog report of the Northwind database.

The attachment called Catalog Formatted.pdf shows the use of conditional formatting on the ProductID control (changing the text colour to Red in this case when the product ID is greater than 40). It also shows the use of a textbox underlying transparent controls whose background colour is set in the On Format event of the detail section to green when ProductID is greater than 60 and to white otherwise.

The On Format event code is just:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.ProductID > 60 Then
        Me.txtBGD.BackColor = &H22B14C
    Else
        Me.txtBGD.BackColor = &HFFFFFF
    End If
End Sub

For test purposes I did not use descriptive constants for the hex colour values shown, which I would normally have done.

The attachment simply called Catalog.pdf shows the original version of page 5 of the report for comparison.

I tested saving the report in print preview mode by pressing Ctrl-S and the design values for the controls concerned were unchanged.

[NeoPa]

Nice Stewart. Thanks for this.

Do you happen to know if this technique works in Access 2003 and earlier too, or is it only a 2007+ facility?

[Stewart Ross]

Works in A2003/2000 as well, NeoPa.

-S

[NeoPa]

Excellent Stewart.

It looks like I'll have to provide an addendum to my article (Why Values in Unbound Form Controls do not Persist) though ;-)

[liztowne]

Thanks Stewart! That is exactly what I was looking for - both solutions worked for me. I thought there might be a way to hard code the text changes in VBA, but wasn't sure where to do the change at. One of these days I'll get serious about learning VBA in Access and not use such an ad-hoc approach :)

Thanks also to Neo-Pa for quick and helpful feedback as well (including clarifications from Stewart).

[NeoPa]

You're welcome Liz.

Actually, I also learned something important in this thread. One of the best thing about volunteering time for this site is that one is surrounded by so many very expert programmers and database designers. I love it :-)

PS. It only works for reports, which is a bit of a blow for the concept as a whole, but is fine and very handy for working on reports. I've added the addendum now if anyone would like to check the linked article again (Why Values in Unbound Form Controls do not Persist).

[Stewart Ross]

Excellent example in your addendum to the linked article, NeoPa - the varying shades associated with the scores shown work very well.

As you rightly point out, this technique works best for reports, which are quite different to forms in their approach to preparing individual rows for display on a page.

Although it is perfectly possible to use similar programmed approaches with single-view forms to change font and background colours this does not work for continuous forms - as the change will be replicated to all rows visible on the form simultaneously, not just the currently-active row. Forms in datasheet view are also unsuitable for this approach.

-Stewart

[NeoPa]

Thanks Stewart. I greatly appreciate your comments :-)

As for Datasheet view, many of the properties for the controls don't work in those at all. Mainly because they aren't actually used if I understand it correctly. It uses the fields directly I believe, though I don't use that option myself so I'm no expert.