Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

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

UPDATE:

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

 

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" 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