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 @)
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
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
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