Mendip Data Systems

Database applications for businesses and schools


Code Samples for Businesses, Schools & Personal Use

Updated 14/02/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:

Add colour to queries, listboxes & combo boxes

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 boxes.and 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:


ColourfulQuery ColourQuery MultiColourCombo&Listbox


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)


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





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





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" to show entered text in 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.


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


Click the image to view a larger version ...

Click the image to view a larger version ...

Click the image to view a larger version ...

Return to Code Samples Page