Add colour to queries, listboxes & combo boxes

Last Updated 14 Feb 2018

The idea in this article was raised in a forum question by a new Access user who assumed everyone knew about it.
However for myself & many others, it was a "well, I never knew that ..." moment

You can change the colour of individual text / number fields in a query or a table like this:


Only the standard colours can be used: Black / Red / Green / Yellow / Blue / Magenta / Cyan / White
Some are clearly more practical than others !!!

To do so, type e.g. ![Red] or ![Blue] in the Format property for a query text field. @[Red] etc also works

For number fields, use e.g. #[Red] or [Blue]# instead

Date fields don't work so well - they change colour but also get displayed as long integer value.
However a work-round is to format the whole query how you want the dates to appear.
You can then format the other fields using e.g. #[Red] or ![Blue] or @[Green] etc

You can of course change the background colours for queries/tables as well

A similar method can be used in forms for combo boxes, list boxesand text boxes.
However in those cases, there is a further restriction as the formatting only seems to work for text fields (using ! or @)


UPDATE:

Many thanks to AWF member CJ_London for the following explanation of formatting options for various datatypes:

=========================================================================

For numbers, there are 4 sections to the Format property: positive; negative; zero; null
So for example [Green]#;[Red]#;[Blue]"Zero";[Cyan]"Nothing Entered" will give you

234
-123
Zero
Nothing Entered

Or you can format as decimals etc.
Note the space after the positive - this is so it will line the number up correctly with negatives, which have a ()

[Green]0.00 ;[Red](0.00);[Blue]"Zero";[Cyan]"Nothing Entered" will give you

 234.12
(123.22)
Zero
Nothing Entered

The last (null) is very useful to provide a prompt to the user in a textbox e.g. "Enter Last Name"

For text strings, there are just 2 sections, not null and null so you use: @;"Enter Last Name"

To get a grey prompt for this example you can set the control forecolor to grey, then the format property could be:   [Black]@;"Enter Last Name"
The user prompt text will look similar to this:     Enter Last Name       but the text entered by the user will be black

Dates are stored as numbers so the format property would be say: dd/mm/yyyy;;;"Enter Date"
However, dates can't be negative or 0 so no point entering format properties for these sections, although I suppose it could highlight erroneous data.

A benefit of using the format property is that you do not change the underlying data - you can still test for null, < or > etc.
The main limitation is the number of colours so its not an alternative to conditional formatting, but certainly has it's uses.

NOTE: For comboboxes, the formatting works on the underlying value (usually a number), not what is displayed (usually text)

=========================================================================

The example below puts many of these ideas into practice. It is deliberately 'over the top'.
However done in moderation, the idea does have value

Click to download:     Colour Query v2              Approx 0.5 MB (zipped)




Return to Code Samples Page                                                           Return to Top