Conditional Formatting in Microsoft Access Reports

Added on: Saturday 29th August 2009

Whilst working on a Microsoft Access project recently, I needed to produce a report with certain fields highlighted in different colours depending on their values. I new I could do this with conditional formatting so I ploughed on and started building the

After I had spent some time on this, I realised that Access only allows three conditions for formatting of cells - presumably because it mirrors field formatting in forms where you have a format for negative, zero or null and positive values.

This was a bit of a blow as I had committed myself to using this method and the report was all but finished.

After the initial panic, I came up with a fairly simple solution that allowed me to use the five different colours that I needed for each field.

All I did was copy the text box control with the three formats and pasted it on top of the original control.

With the new control renamed I then removed the three conditional formats and added the two extra that I needed.

All that remained was to make sure that the new control only displayed if the value in the field matched one of the two new conditions - one line of code in the Detail_Format event.

My conditions were actually based on the value of a hidden field on the report so the code was simply:

Me!field2.Visible = (Me!hiddenValue > 3)

where field2 is the text box containing the formatting and hiddenValue is the hidden control that is the criteria for the formatting.

Both field2 and the original control had the same recordsource so when the hiddenValue was greater than 3, field2 displayed exactly the same data as the original control but because it was on top, it hid the original.

This worked fine and in fact could be extended to use 10, 20 or more conditions just by adding more duplicate controls and using the Format event to control their display.

NOTE: I had thought originally I could have set the Back Style property of the text boxes to Transparent to hide them without using any code at all but because it was the Back Colour property that was being changed in the formatting this didn't work. Changing the Back Colour when the Back Style is Transparent has no effect.

The report itself was quite a challenge in the first place which is why I didn't want to abandon what I had done for another solution. I will publish another article covering the whole report in due course.

Permalink