Results 1 to 7 of 7
  1. #1
    PsYc0TiC is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    35

    Conditional Formatting in a Query Expression Help

    Hello all,



    I have a report that is generated by a query. Inside that query is an expression that calculates for a percentage. It looks like this:

    Code:
    [PercentVisInspected: IIf([TotalVisInspected]=0,0,[TotalVisInspected]/[TotalReceived])
    The target percentage number I am looking for with this report is 10% of the "TotalReceived". The format for the expression is percentage (of course)

    I have been trying to get conditional formatting to make the output on the form show green bold if the number is 10% or higher and red bold if it is less than 10% but I am not smart enough.

    Will someone please help me?

  2. #2
    PsYc0TiC is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    35
    I think I said all that wrong. When trying to make the conditional formatting rule I choose

    Code:
    "Expression is" and I choose the [PercentVisInspected]>=10 and choose bold green
    Then create another rule I choose

    Code:
     "Expression is" and I choose the [PercentVisInspected] <10 and I choose bold red
    Not working of course
    Last edited by PsYc0TiC; 08-29-2016 at 01:41 PM. Reason: spelling

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    If the conditional formatting is on the control containing [PercentVisInspected], then try using "Field Value Is" instead of "Expression is"

    More importantly, while the format of the control may be set to percentage, the value in the control isn't.

    The value in the control is [TotalVisInspected]/[TotalReceived], which is always <= 1, so you have to base your conditional format on a range of 0 to 1, not 0 to 100.

  4. #4
    PsYc0TiC is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    35
    Ok I used "Field Value Is" and I tried this...

    Click image for larger version. 

Name:	Capture.JPG 
Views:	13 
Size:	37.3 KB 
ID:	25611

    So all that are 0% show grey... all that are 9% or less show red and all that are 10% or more show green but it stops processing the rules at the red and doesn't make the green work.
    Click image for larger version. 

Name:	Capture1.JPG 
Views:	13 
Size:	16.4 KB 
ID:	25612
    Last edited by PsYc0TiC; 08-29-2016 at 02:21 PM. Reason: forgot something

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You missed something very important what John said:
    The value in the control is [TotalVisInspected]/[TotalReceived], which is always <= 1, so you have to base your conditional format on a range of 0 to 1, not 0 to 100.
    10% is really 0.1
    100% is really 1

    So your value need to reflect that.
    Code:
    Value is >= .10
    Value is <= .09

  6. #6
    PsYc0TiC is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    35
    Ah... I did miss that. Thank you very much.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure if your values all work to an exact percentage (i.e. 8%, 20%) or can have decimals (i.e. 8.5%, 12.4%) or whether it is intended anyway. But if the latter then anything greater than 9% and less than 10% will not be conditionally formatted.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access Form Conditional Formatting Expression
    By mrmmickle1 in forum Programming
    Replies: 1
    Last Post: 08-03-2016, 02:01 PM
  2. Replies: 18
    Last Post: 01-07-2016, 11:37 PM
  3. Conditional Formatting using "Expression is:"
    By TToc2u in forum Programming
    Replies: 1
    Last Post: 07-20-2013, 08:05 PM
  4. Replies: 5
    Last Post: 03-14-2012, 08:32 PM
  5. conditional formatting expression
    By kroenc17 in forum Programming
    Replies: 2
    Last Post: 10-22-2010, 09:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums