TIP: Add colour to queries, listboxes & comboboxes

[isladogs]

NOTE:
This is similar to the ideas in a thread I posted recently at: https://www.access-programmers.co.uk/forums/showthread.php?t=297618

The idea in this post was explained by an Access newbie in another forum who assumed everyone knew about it.
However for myself & many others, it was a "well, I never knew that ..." moment

I've copied it to the repository so its easier to find for reference in the future

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

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

So its probably of limited use only e.g. for highlighting a query column in a presentation or forum post answer ...

To do so, type e.g. ![Red] or ![Blue] in the Format property for the query 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 for combo boxes and listboxes.
However in those cases, there is a further restriction as the formatting only seems to work for text fields (using ! or @)

Overall, an interesting idea but not quite as useful as it might have been
Perhaps more a novelty than of widespread use...

 

[CJ_London]

I've been banging on about this for ages

you can do a lot more

for numbers, there are 4 sections to the format property (not the function)

positive; negative; zero; null

so for example [Green]#;[Red]#;[Blue]"Zero";[Cyan]"Nothing Entered" will give you

234
-123
Zero
Nothing Entered

or you 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

but not quite as useful as it might have been

The last (null) is very useful to provide a prompt to the user - e.g. "Enter a number" - for forms of the right design you don't even need labels - listing the different elements of an address for example

For strings, there are just 2 sections, not null and null so you use

@;"Enter Town"

to get a grey prompt for this example you can set the control forecolor to grey, then format property could be

[Black]@;"Enter Town"

to show entered text in black

dates are numeric so the format property would be say

dd/mm/yyyy;;;"Enter Date"

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.

Only limitation really is the number of colours and you cannot affect the background, so not a real contender as 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)

 

[isladogs]

I've been banging on about this for ages

you can do a lot more

Thanks for explaining all that so clearly
I had achieved some of that myself but hadn't been able to understand what was going on until now.

Anyway, following the instructions in CJ London's post, I've created the following, truly hideous result!

Definitely overdone methinks! However, used in moderation, I think this does have value

I just hope we don't start seeing too many examples like the screenshot above