Results 1 to 10 of 10
  1. #1
    martyo is offline Novice
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    5

    Conditional Formatting not working with one rule but working with another rule

    I have 2 simple rules on a text box control. If the value is >= 120% the text is formatted red, if the value is <= 80% the text box is formatted with blue shading. The values greater than 120% work fine, but the values less that 80% will not format as blue shading. In addition, both rules work fine with reports with this same conditional formatting that only have one query within the report query... it seems the reports where I have multiple queries within the report query is where I'm having a problem.


    I have tried everything.
    Any suggestions would be greatly appreciated.

    Kind regards,
    Marty

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What do you mean by "multiple queries within the report query"? No idea why more complicated query would cause issue.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    martyo is offline Novice
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    5

    Conditional Formatting not working with one rule but working with another rule-attached db

    What do you mean by "multiple queries within the report query"?
    Sorry, I guess that was vague. On report "rptCampusTotals_Summary_N_4a and 4b_landscape" the control source is "
    qryCampusTotals_Summary_N_Opt_4a" which has one table joined with several queries. This is the one that the conditional formatting for the blue shading rule is not working.
    On report "
    rptCampusTotals_ELZN_NORTH_LANDSCAPE" the control source is "qryCampusTotals_ELZN_NORTH_wTrnfs" which has one table joined with one query. This one works just fine.
    They both have the same simple conditional rules.
    Thanks for taking a look.
    Marty
    Attached Files Attached Files

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Field formats the same?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have tried everything.
    I have yet to see a situation where this was true.
    Conditional formatting of background color will not work when the control background is set to transparent.
    Last edited by Micron; 05-08-2022 at 08:16 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You certainly seem to have designed your tables like spreadsheets (wide) rather than tall (relational database style). We call that 'suffering from Excelitis'. It's one reason why you're forced to create so many queries. Instead of one query that can use area criteria, you need a query for every area because different areas are in different fields of the same table (one of which has no primary key). That and using real data as PK fields but having no relationships set means you can end up with orphaned records where the related records are no longer "linked" to the main record if the main record key value is edited. You might want to research 'database normalization' and start over if it convinces you to do so. Otherwise, I see more problem posts in your future!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    martyo is offline Novice
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    5

    Conditional Formatting not working with one rule but working with another rule - SOLVED!

    Quote Originally Posted by Micron View Post
    I have yet to see a situation where this was true.
    Conditional formatting of background color will not work when the control background is set to transparent.
    That's it!! Thank you so much!! I thought I had checked everything.

    Marty

  8. #8
    martyo is offline Novice
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    5
    This is a special situation where Access is linked to a table in our GIS mapping software where we use a redistricting tool and that tool limits the ability to normalize for this situation. But thank you for taking the time to respond. Another post suggested that I check the background setting on the fields, and that they are not set to transparent, and that solved my issue when I set them as Normal

    Thanks,
    Marty

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    that tool limits the ability to normalize for this situation.
    Again, seldom true. The "fix" for such incoming data constraints is to leave the source layout as is, either via linked tables in Access, or an Access table that replicates the layout. Then you use update/append queries to put things in their proper places within Access tables that you normalized. There are many benefits to doing this, the least of which might be that you're able to create one query and one report for umpteen area searches.
    Another post suggested that I check the background setting on the fields, and that they are not set to transparent,
    You mean in another forum? Because I did mention that here.
    Last edited by Micron; 05-08-2022 at 10:22 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    martyo is offline Novice
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    5
    It was a reply in this forum to my original post.

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

Similar Threads

  1. Conditional formatting not recognizing rule
    By GraeagleBill in forum Forms
    Replies: 12
    Last Post: 04-06-2022, 11:47 AM
  2. Conditional formatting *almost* working
    By skyatis in forum Programming
    Replies: 2
    Last Post: 01-27-2017, 01:55 PM
  3. Replies: 1
    Last Post: 11-19-2014, 07:19 AM
  4. Conditional Formatting not working properly
    By GraeagleBill in forum Reports
    Replies: 3
    Last Post: 03-08-2014, 07:21 PM
  5. Validation Rule Not Working??
    By tylerg11 in forum Forms
    Replies: 3
    Last Post: 12-23-2011, 02:22 PM

Tags for this Thread

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