We help IT Professionals succeed at work.

Trying to change color of text in Access form on textbox using conditional formatting.

Jarred Meyer
Jarred Meyer asked
on
342 Views
Last Modified: 2012-06-27
I have a textbox called RESULT. It is always a number that is keyed in here.

There is also a PLUS field and a MINUS field that both return numbers.

I want to set conditional formatting to say that when the number in RESULT is greater than PLUS or LESS than MINUS then highlight the text red.

I started out by just trying to get the plus side working so I created this;
plus
It seems to have a problem with numbers that contain decimals.

So when I have a RESULT that is .940 and the PLUS number is .950 it will not highlight the RESULT Red until it is 1.000 or greater.. I would expect it to highlight when it is greater than .950.. So .9501 should highlight red.

Thanks in advance for the help!
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
try, using Expression is

expression is [Result]>[Plus] Or [Result]<[Minus]
CERTIFIED EXPERT
Top Expert 2016

Commented:
<RESULT that is .940 and the PLUS number is .950 it will not highlight the RESULT Red>

.940 is less than .950
Jarred MeyerProduction Manager

Author

Commented:
I tried that initially and its giving me some really wierd results..

Here is a screenshot of what's being highlighted..
te
Jarred MeyerProduction Manager

Author

Commented:
<RESULT that is .940 and the PLUS number is .950 it will not highlight the RESULT Red>

Sorry that may have not made any since..

Basically when the result is not falling within the range then I would like for it to highlight red..
Jarred MeyerProduction Manager

Author

Commented:
Oh and in the picture the plus and minus labels should be switched..
Jarred MeyerProduction Manager

Author

Commented:
Maybe my PLUS and MINUS fields are note being treated as numbers but rather text? They are created using some VBA that I'm still tweaking on another question.

Can I do something like set up a textbox that will convert them to a number that goes to 4 decimal places?

NewPLUS= ToNumber([PLUS], 0000.0000) ? Something like this perhaps?
CERTIFIED EXPERT
Top Expert 2016

Commented:
<RESULT is greater than PLUS or LESS than MINUS then highlight the text red.>

setting
                   expression is [Result]>[Plus] Or [Result]<[Minus]

<Basically when the result is not falling within the range then I would like for it to highlight red..>

the setting will be

                 expression is [Result]>[Plus] And [Result]<[Minus]
Jarred MeyerProduction Manager

Author

Commented:
Changing it to this doesn't seem to make it highlight anything.. Here is some numbers I through in to see;
numbers
Here is the current formatting;
formatting
CERTIFIED EXPERT
Top Expert 2016

Commented:
sorry

the setting will be

                 expression is [Minus] > [Result] Or [Plus] < [Result]
the same as

                 expression is [Result]>[Plus] Or [Result]<[Minus]
CERTIFIED EXPERT
Top Expert 2009

Commented:
What is the data type of these fields?  If they are text fields, you could convert their values to Double values for comparison.
Jarred MeyerProduction Manager

Author

Commented:
I'm convinced these fields need to be converted to numbers.. Not sure how to do that though.. It needs to handle up to 0000.0000 if possible..
Jarred MeyerProduction Manager

Author

Commented:
Oh and capricorn in response to your latest expression;

expression is [Minus] > [Result] Or [Plus] < [Result]

Here is some results that it is giving me..;

numbers
CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2016

Commented:
or this

expression is CDbl([Minus]) > CDbl([Result]) Or CDbl([Plus]) < CDbl([Result])
Jarred MeyerProduction Manager

Author

Commented:
That did the trick! Now its working:)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.