Solved

Microsoft Access - Conditional Formatting in Forms Not Working

Posted on 2013-11-01
16
2,900 Views
Last Modified: 2013-11-01
Hello Experts,

I am currently designing a form in MS Access.

The user selects a product and that product has a target output.  That target is displayed for the user on the form.

The user enters the actual output and I am trying to get the conditional formatting to colour either red or green, depending on whether the actual is greater or less than the target.

For some reason, the formatting is active, but sometimes incorrect.  Example -

Target - 2520 units per hour.  Anything equal to or above is green, anything below is red.

Actual - 100 (showing red - correct)
Actual - 29 (showing green - incorrect)
Actual - 2400 (showing red - correct)
Actual - 500 (showing green - incorrect)

Any idea as to why the rules are not being adhered to consistently?

Thanks,

diana_g
0
Comment
Question by:diana_g
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +2
16 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39616909
The sorting is somehow working out alphabetically instead of numerically.  Try setting your Conditional Formatting using "Expression IS", and enter this as the expression:

CInt([YourFieldName]) => 2520

And set the colors appropriately.

Alternatively, force a similar conversion to integer in your form/report query.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39616916
what Data type are the field for the Actual Output and Target?

both should be Number..
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39616920
can you show show the conditional formatting, take a screen shot, and post that here as an image (last control on the toolbar)?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:mbizup
ID: 39616935
Slight correction to my earlier comment  ( >= instead of =>), and if you are using a textbox on your form instead of a fixed integer value of 2520, use this for your conditional formatting expression:


CInt([YourFieldName]) >= CInt([YourTargetTextBoxName])
0
 

Author Comment

by:diana_g
ID: 39616971
Hello Experts,

Thank you for your suggestions.  @mbizup and @capricorn1 - I did try your suggestions, with no success.  Perhaps I have missed something.

Data type for the fields is a number.

Here is a screenshot.
Screenshot.bmp
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39616979
post the image of Conditional formatting you created


.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39616983
Since you have a mainform/subform setup, use this for the formatting expression:



CInt([YourFieldName]) >= CInt(Forms!YourMainFormName![YourTargetTextBoxName])
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39616987
not what I meant.  Open the form in design view, select the control with the formatting, the click the conditional formatting button on the ribbon.  Get a screen shot of the conditional formatting popup form so we can see precisely what the conditional format definition looks like.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39616988
Also, if the text "Units Per Hour" is actually included in the textbox data, use VAL instead of CInt:

CInt([YourFieldName]) >= Val(Forms!YourMainFormName![YourTargetTextBoxName])
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39616990
First...Make sure you first clear any existing CF on any and all of the individual controls

Then make sure you select all the control you want formatted *at the same time*, and set the conditional formatting for all of them this way...

This all works fine for me...
0
 

Author Comment

by:diana_g
ID: 39617049
Hello Experts,

To clarify, it is not a subform.  This is all on one form.

See attached for conditional formatting conditions.
Screenshot2.bmp
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39617063
Did you Try changing the Condition to "Expression Is", and using CInt() to force the values to integer?

The expression would be for the green:

CInt([YourFieldName]) >= CInt([TPR])

and for the red:

CInt([YourFieldName]) <  CInt([TPR])
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39617064
Again, this works fine for me, here is the output:
sample
and here is the CF:
CF
Note that all the controls are selected and the formatting rule is set for all of them only once.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39617129
In the screenshot of your form, I believe you have the [TPR] field displayed in the upper right corner, but it is impossible to tell whether the 'units per hour' is part of the value of a field, or a label that simply follows the control.

Please confirm that the [TPR] control is bound to a field in the forms recordsource that is is a numeric data type.  If not you may want to try:

>= Val(Forms!YourMainFormName![YourTargetTextBoxName])

or, as mbizup mentioned above,  change the test from "Field value is" to "Expression is"  and set the expression as she described it above.

CInt([One]) >= CInt(Forms!YourMainFormName![TPR])
CInt([Two]) >= CInt(Forms!YourMainFormName![TPR])
...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39617241
Please investigate the other experts posts first, I think they are on to something...

FWIW, here is the simple sample I created that had his working...
Database184.mdb
0
 

Author Closing Comment

by:diana_g
ID: 39617892
Thank you @mbizup.  This solution worked very well.  Thank you to all the other experts for your feedback.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can I check if a field is null and whether it is or not, still run the code 40 69
Error 438 6 48
Access vs Access runtime 6 58
Calculation in a Report 13 35
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question