Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-29-2017, 03:31 PM   #1
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,233
Thanks: 115
Thanked 3,072 Times in 2,790 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Colourful query

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/for...d.php?t=203485
Attached Images
File Type: png ColourfulQuery.PNG (46.3 KB, 511 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 12-29-2017 at 04:12 PM.
isladogs is offline   Reply With Quote
The Following 3 Users Say Thank You to isladogs For This Useful Post:
Frothingslosh (01-22-2018), grubnz (12-30-2017), HGCanada (01-13-2018)
Old 12-29-2017, 10:23 PM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,676
Thanks: 93
Thanked 1,701 Times in 1,574 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Colourful query

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 12-30-2017, 08:05 AM   #3
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,233
Thanks: 115
Thanked 3,072 Times in 2,790 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Colourful 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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Minty (01-15-2018)
Old 12-30-2017, 03:11 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,298
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Colourful query

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 12-30-2017, 03:40 PM   #5
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,233
Thanks: 115
Thanked 3,072 Times in 2,790 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Colourful query

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 12-31-2017, 05:08 AM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,233
Thanks: 115
Thanked 3,072 Times in 2,790 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Colourful query

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/msacce...mboboxcontrols

The attached zip file contains a short AVI video showing this - also from the same site
Attached Images
File Type: png MultiColourCombo&Listbox.PNG (17.5 KB, 401 views)
Attached Files
File Type: accdb ListBoxColumnsColors.accdb (424.0 KB, 105 views)
File Type: zip video_RainbowListBox.zip (614.1 KB, 39 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 01-01-2018 at 06:17 PM.
isladogs is offline   Reply With Quote
Old 12-31-2017, 09:55 AM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,233
Thanks: 115
Thanked 3,072 Times in 2,790 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Colourful query

@Pat

Quote:
It colorizes combos and listboxes but only seems to work for the ! version.
Is this what you meant?

Attached Images
File Type: png Screenshot 2017-12-31 17.49.33.png (43.4 KB, 1671 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 01-01-2018, 01:47 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,298
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Colourful query

Yes but I haven't been able to play with it. It is New Year's weekend after all.

Happy New Year!!!
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 01-13-2018, 11:29 PM   #9
HGCanada
Newly Registered User
 
Join Date: Dec 2016
Posts: 76
Thanks: 60
Thanked 0 Times in 0 Posts
HGCanada is on a distinguished road
Re: Colourful query

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?
HGCanada is offline   Reply With Quote
Old 01-14-2018, 01:38 AM   #10
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,233
Thanks: 115
Thanked 3,072 Times in 2,790 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Colourful query

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!
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 01-14-2018 at 01:07 PM.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
HGCanada (01-15-2018)
Old 01-15-2018, 07:18 AM   #11
HGCanada
Newly Registered User
 
Join Date: Dec 2016
Posts: 76
Thanks: 60
Thanked 0 Times in 0 Posts
HGCanada is on a distinguished road
Re: Colourful query

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!
HGCanada is offline   Reply With Quote
Old 01-15-2018, 07:58 AM   #12
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,233
Thanks: 115
Thanked 3,072 Times in 2,790 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Colourful query

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 01-15-2018, 08:14 AM   #13
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,437
Thanks: 166
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Colourful query

Well - another "I didn't know about that" vote.
If nothing else it will brighten up some dull looking list boxes
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 01-15-2018, 10:15 AM   #14
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,233
Thanks: 115
Thanked 3,072 Times in 2,790 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Colourful query

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...d.php?t=297833
Attached Images
File Type: png ColourfulQuery.PNG (15.7 KB, 1518 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Reply

Tags
colour a query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
create a colourful clickable schedule shutzy Forms 7 04-03-2012 12:52 AM




All times are GMT -8. The time now is 02:02 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World