Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-20-2018, 06:36 AM   #1
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,177
Thanks: 83
Thanked 1,520 Times in 1,419 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
TIP: Add colour to queries, listboxes & comboboxes

NOTE:
This is similar to the ideas in a thread I posted recently at: https://www.access-programmers.co.uk...d.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...
Attached Images
File Type: png ColourfulQuery.PNG (46.3 KB, 634 views)

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

New example databases:

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.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est

Last edited by ridders; 01-20-2018 at 02:33 PM.
ridders is offline   Reply With Quote
The Following 4 Users Say Thank You to ridders For This Useful Post:
DBQueen (04-22-2018), Frothingslosh (01-22-2018), Lightwave (02-07-2018), Snowflake68 (02-26-2018)
Old 02-12-2018, 05:17 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,195
Thanks: 39
Thanked 3,299 Times in 3,195 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: TIP: Add colour to queries, listboxes & comboboxes

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



Quote:
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)
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button

Last edited by CJ_London; 02-12-2018 at 05:25 PM.
CJ_London is offline   Reply With Quote
The Following 3 Users Say Thank You to CJ_London For This Useful Post:
jdraw (02-12-2018), Minty (02-13-2018), ridders (02-14-2018)
Old 02-14-2018, 04:18 AM   #3
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,177
Thanks: 83
Thanked 1,520 Times in 1,419 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
Re: TIP: Add colour to queries, listboxes & comboboxes

Quote:
Originally Posted by CJ_London View Post
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
Attached Images
File Type: png ColourQuery.PNG (21.3 KB, 484 views)
Attached Files
File Type: zip ColourQuery v2.zip (21.9 KB, 69 views)

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

New example databases:

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.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Clearing Comboboxes and Listboxes cosmarchy Modules & VBA 8 05-17-2013 04:50 AM
Multipul Multiselect-Listboxes and queries Rat1sully Queries 1 05-21-2012 01:52 AM
Hi! I need help with comboboxes, listboxes... Keidy Forms 12 12-08-2006 09:49 AM
Comboboxes and Listboxes Switchwork Forms 4 12-22-2003 02:31 PM
Multi-Select Listboxes and Queries and Reports, Oh My! DataMiner Modules & VBA 4 09-10-2002 07:47 PM




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


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

Sponsored Links

How to advertise

Media Kit


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