Colourful query (1 Viewer)

isladogs

MVP / VIP
Local time
Today, 11:40
Joined
Jan 14, 2017
Messages
18,186
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:



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,064
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:40
Joined
Feb 28, 2001
Messages
26,996
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.
 

isladogs

MVP / VIP
Local time
Today, 11:40
Joined
Jan 14, 2017
Messages
18,186
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:40
Joined
Feb 19, 2002
Messages
42,970
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.
 

isladogs

MVP / VIP
Local time
Today, 11:40
Joined
Jan 14, 2017
Messages
18,186
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
 

isladogs

MVP / VIP
Local time
Today, 11:40
Joined
Jan 14, 2017
Messages
18,186
Here's an example I found online which shows coloured columns in both listboxes & combos:



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

  • ListBoxColumnsColors.accdb
    424 KB · Views: 250
  • MultiColourCombo&Listbox.PNG
    MultiColourCombo&Listbox.PNG
    17.5 KB · Views: 848
  • video_RainbowListBox.zip
    614.1 KB · Views: 162
Last edited:

isladogs

MVP / VIP
Local time
Today, 11:40
Joined
Jan 14, 2017
Messages
18,186
@Pat

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

Is this what you meant?

 

Attachments

  • Screenshot 2017-12-31 17.49.33.png
    Screenshot 2017-12-31 17.49.33.png
    43.4 KB · Views: 4,034

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:40
Joined
Feb 19, 2002
Messages
42,970
Yes but I haven't been able to play with it. It is New Year's weekend after all.

Happy New Year!!!
 

HGCanada

Registered User.
Local time
Today, 07:40
Joined
Dec 30, 2016
Messages
82
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?
 

isladogs

MVP / VIP
Local time
Today, 11:40
Joined
Jan 14, 2017
Messages
18,186
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:

HGCanada

Registered User.
Local time
Today, 07:40
Joined
Dec 30, 2016
Messages
82
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!
 

isladogs

MVP / VIP
Local time
Today, 11:40
Joined
Jan 14, 2017
Messages
18,186
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
 

Minty

AWF VIP
Local time
Today, 11:40
Joined
Jul 26, 2013
Messages
10,354
Well - another "I didn't know about that" vote.
If nothing else it will brighten up some dull looking list boxes :)
 

isladogs

MVP / VIP
Local time
Today, 11:40
Joined
Jan 14, 2017
Messages
18,186
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 ...



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: 3,893

Users who are viewing this thread

Top Bottom