Colourful query

isladogs

isladogs

MVP / VIP
Local time
Today, 12:20
Joined
Jan 14, 2017
Messages
18,840
Just picked up this idea from another forum.
Apologies if this hint has been posted previously by others

Not sure how much use it is but I'll leave others to decide for themselves!
You can change the colour of individual text fields in a query like so:

attachment.php


To do so, type e.g. ![Red] or ![Blue] in the Format property for the query field.
For number fields, use e.g. #[Red] or [Blue]# instead
Date fields don't work - they change colour but also get displayed as long integer value

NOTE:
It also works for table fields
Other effects like Bold / Italic do not work

EDIT:
Just found an earlier thread where missinglinq described the same idea for a combobox
https://access-programmers.co.uk/forums/showthread.php?t=203485
 

Attachments

  • ColourfulQuery.PNG
    ColourfulQuery.PNG
    46.3 KB · Views: 1,262
Last edited:
Next question is, do you have the ability to put a color number or color function in that position? It's hard to read ![White] on a good day unless you are doing some creative color backgrounds for the query.
 
AFAIK this is all you can do in terms of formatting the field contents in a query.

Only the standard colours can be used:
Black / Red / Green / Yellow / Blue / Magenta / Cyan / White

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

You can of course change the background colours for queries as well
 
AMAZING!!!!!!
It colorizes combos and listboxes but only seems to work for the ! version.

I tried to upload a picture but my internet is flaky today.
 
It was a "Well I never knew that ... " moment when I first heard of it.

I've only got it to work in queries & tables - more a novelty than of real use ...

When I tried on a listbox, the colouring worked fine when I set it on the record source but I've been unable to save the format settings when I view the form.
Similarly for combo box

If you got that to work, I'd be very interested - particularly for a listbox
 
Here's an example I found online which shows coloured columns in both listboxes & combos:

attachment.php


The 'trick' is to set the colours in the source table (or query)
NOTE: The number fields take the same colour as the default for each control.
Any colour formatting for those fields is ignored on the form controls.

The original version of this is by Pavlo Pedan & can be found here:
https://sites.google.com/site/msaccesscode/controls-2/rainbowlistboxhowtosetdifferentcolorsforcolumnsofalistboxandacomboboxcontrols

The attached zip file contains a short AVI video showing this - also from the same site
 

Attachments

Last edited:
@Pat

It colorizes combos and listboxes but only seems to work for the ! version.
Click to expand...

Is this what you meant?

attachment.php
 

Attachments

  • Screenshot 2017-12-31 17.49.33.png
    Screenshot 2017-12-31 17.49.33.png
    43.4 KB · Views: 4,926
Yes but I haven't been able to play with it. It is New Year's weekend after all.

Happy New Year!!!
 
This works great for my queries!

Did anybody find a way to change the font colour of date fields, without changing them to long integer format?
 
Not if you keep it as a date field.
You could change it to text field in your query using CStr(DateField) and format that....

As I said earlier, this is an interesting feature that seems to be only partially implemented by MS.
As it stands, I think little more than a novelty though useful to highlight a field when responding to forum posts!
 
Last edited:
Thanks. Well, I have to say it's helpful for me even if it's just for text and number fields. I have a query output I want the team to see, and there are so many columns, it's nice to just have some way to visually divide up the output. Very helpful tip. Thanks for posting it!
 
You're welcome.

I had been using Access for almost 20 years and only recently found out about this feature from a chance 'passing comment' by a new user of Access!

Don't forget it can also be used in tables & listboxes & comboboxes.
However, it's a real shame it has major limitations
 
Well - another "I didn't know about that" vote.
If nothing else it will brighten up some dull looking list boxes :)
 
Ironically the 'newbie' who first explained how to do this assumed everyone knew about it...

In answer to HG Canada's earlier question about colour formatting dates in queries, it is possible ...

attachment.php


Unlike text or number fields, you can't do it directly using format codes ... well you can but it changes the date to its long integer value

Instead 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]

For info, I've just posted a new thread explaining a keyboard shortcut used to fill down values from the previous record in a table, query or form.
https://www.access-programmers.co.uk/forums/showthread.php?t=297833
 

Attachments

  • ColourfulQuery.PNG
    ColourfulQuery.PNG
    15.7 KB · Views: 4,788
You must log in or register to reply here.

Similar threads

G
Again, query where dealing with empty fields
Replies
13
Views
531
theDBguy
theDBguy
O
Is it possible to create a query of queries without using UNION
2
Replies
24
Views
924
Pat Hartman
P
J
Creating a Running Balance Query
2 3
Replies
54
Views
1,596
AngelSpeaks
A
N
Solved Calculation Query
Replies
2
Views
181
Number11
N
essaytee
Is it possible: Query/SQL to dynamically display a record/order/position number, without underlying field from a table
Replies
11
Views
519
essaytee
essaytee

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom