Page 1 of 2 12 LastLast
Results 1 to 15 of 30

Conditional Formatting in Access

  1. #1
    Triland's Avatar
    Triland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    37

    Conditional Formatting in Access

    I am working on an Access report and want to have the system add conditional formatting (shading) to the details area if the value of the field "Revisions" is the Max value for all revisions in the report.

  2. #2
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,779
    Have a field in report that has this Max value. Refer to that textbox in the ConditionalFormatting expression for the Revisions textbox.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    Triland's Avatar
    Triland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    37
    The only problem with that is for each instance in the report the latest 'Revision' number will be different. Any idea of how I might work a round that?

  4. #4
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,779
    Don't understand. If the Revision number does not equal the 'Max' value then highlight it or only highlight the number that is equal to the 'Max'.

    What do you mean by 'instance' - a document item? What is this report showing? If it has only the 'lastest' revision for each item then why the need to highlight?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    Triland's Avatar
    Triland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    37
    Revision Cable No. Quantity Text130 Text131 Text132 Field96 Field101 Field97 Field102 Comments Text110 Circuit Voltage Field95 Field98 Field99 Text119 Text120 Text121 Text122 Text123 Text124 Text125
    0 C0050 1 600 5/C 14 1GES-470 1SCS-0970 125.00 C05014 480V LOAD CENTER SWGR 480V LOAD CENTER SWGR
    0 E0050-1 1 600 1/C 4/0 1GES-470 0GES-471A 480.00 E014/0 480V LOAD CENTER SWGR 480V LOAD CENTER SWGR T4102 T4107 T4108 K3031 K3055 K3075
    0 E0050-2 1 600 1/C 4/0 1GES-470 0GES-471A 480.00 E014/0 480V LOAD CENTER SWGR 480V LOAD CENTER SWGR T4102 T4107 T4108 K3032 K3068 K3076
    0 E0050-3 1 600 1/C 4/0 1GES-470 0GES-471A 480.00 E014/0 480V LOAD CENTER SWGR 480V LOAD CENTER SWGR T4102 T4107 T4108 K3033 K3071 K3077
    0 E0050-4 1 600 1/C 4/0 1GES-470 0GES-471A 480.00 E014/0 480V LOAD CENTER SWGR 480V LOAD CENTER SWGR T4102 T4107 T4108 K3034 K3072 K3078
    0 H0050 1 600 4PR-SH 16 1GES-470 1SCS-0970 24.00 G04P16SR 480V LOAD CENTER SWGR 480V LOAD CENTER SWGR
    0 P0050-1 3 600 1/C 500 1GES-470 0GES-471A 480.00 B01500 480V LOAD CENTER SWGR 480V LOAD CENTER SWGR T4102 T4107 T4108 K3031 K3055 K3075
    0 P0050-2 3 600 1/C 500 1GES-470 0GES-471A 480.00 B01500 480V LOAD CENTER SWGR 480V LOAD CENTER SWGR T4102 T4107 T4108 K3032 K3068 K3076
    0 P0050-3 3 600 1/C 500 1GES-470 0GES-471A 480.00 B01500 480V LOAD CENTER SWGR 480V LOAD CENTER SWGR T4102 T4107 T4108 K3033 K3071 K3077
    0 P0050-4 3 600 1/C 500 1GES-470 0GES-471A 480.00 B01500 480V LOAD CENTER SWGR 480V LOAD CENTER SWGR T4102 T4107 T4108 K3034 K3072 K3078
    0 (this is the revision number C0136 1 600 12/C 14 1SCS-0970 0EEG-610CP 120.00 C12014 SUPERVISORY CNTL SYS (SCS) SUPERVISORY CNTL SYS (SCS) T1103 T1104 K1041 K1034 K1037 K1048 CONDUIT
    1 C0136A 1 600 12/C 14 1SCS-0970 0EEG-610CP 120.00 C12014 SUPERVISORY CNTL SYS (SCS) SUPERVISORY CNTL SYS (SCS) T1103 T1104 K1041 K1034 K1037 K1048 CONDUIT

    So what happens is the report runs and will show all of the different Revisions. My boss wants to see just the Greatest Revision number (highes value) highlighted in the report. Hopefully that's a little clearer. And, thanks for working with me on this.

  6. #6
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,779
    The highest revision number I see is 1. That is the value that should be highlighted? What if more than one record has revision 1?

    What is being 'versioned' - the CableNo?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    Triland's Avatar
    Triland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    37
    Any Ideas???

  8. #8
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,779
    My idea is to calculate the Max value displayed on the report then use it in ConditionalFormatting expression. Nothing you have shown gives me any different understanding to recommend otherwise.

    You did not answer questions in last post.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  9. #9
    Triland's Avatar
    Triland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    37
    OK. HMMM. I have a report that lists all of the revisions for a project. So for example: revision 1, revision 2, revision 3 and so on. In this instance on only want revision 3 to be shaded because it is the latest revision and has the greatest value.

  10. #10
    Triland's Avatar
    Triland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    37
    I posted an example in the response at 1:54.

  11. #11
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    26,779
    Don't know where that post went but I found another thread on this issue posted at 12:45 and deleted it.

    So you need to calculate the Max revision for each project? Possible options:

    1. A DLookup in query to construct a field that will then be available as any other field in the report. The value will show for every project record.

    2. An aggregate (GROUP BY) query that returns the Max revision for each project then join that query to table/or query with the detail records. Again, the Max value will show on every record because of the join and field is available in the report.

    3. Set up a Group section in report on the ProjectID. Textbox in that Group section with expression: Max(Revision)
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  12. #12
    Triland's Avatar
    Triland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    37
    Thank you so much. I'm going to try this and reply and soon as I get it fixed :-)!!!!

  13. #13
    Triland's Avatar
    Triland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    37
    I have added the group and the field. Can you tell what the coding would look like to get it to return that MAX revision number in that report?

  14. #14
    Triland's Avatar
    Triland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    37
    Can someone please help. I have a simple report that pulls in Revision numbers. I simply want the conditional formatting in Access to only highligt the greatest (or highest) revision number. So the report would show Rev = 1, Rev = 2 and so on. In that instance only revision 2 should be highlighted b/c it has the highest value. I would like the report to continue to use that logic moving forward and higher and higher revision numbers are added. Any ideas? I tried and few things and nothing seems to work.

  15. #15
    Triland's Avatar
    Triland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2013
    Posts
    37
    Hi, I added the textbox and the coding: MAX(revisions). It is accepted ok but does not pull anything into the field. Any idea of what I might be doing wrong.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 10-11-2012, 01:13 PM
  2. Conditional Formatting
    By bellevue in forum Forms
    Replies: 4
    Last Post: 05-17-2012, 05:03 AM
  3. Access Conditional formatting
    By mailboy in forum Access
    Replies: 1
    Last Post: 12-16-2011, 11:34 PM
  4. Conditional Formatting
    By Paul H in forum Reports
    Replies: 3
    Last Post: 11-07-2011, 09:59 AM
  5. Conditional Formatting
    By Desstro in forum Programming
    Replies: 3
    Last Post: 12-01-2010, 07:52 PM

Posting Permissions

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