Results 1 to 5 of 5
  1. #1
    cbrsix is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    214

    Report Groups and Sort

    I have a report that Groups by Rep Name and Sorts by State. I have it set so the Group shows only when Rep Name is Active. My problem is that the Rep name doesn't show up(like I want) but the States are still there. How do I get the states to only appear if the Group (Rep) is included? The first screen shot show the design view of the report and the grouping. The second is a view from the report view. There would normally be a Rep name under each subtotal.



    Thanks.

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Instead of using the IIF in the Rep header, go to the ON FORMAT event of that header (click on the gray bar that says [Rep LName] & ", " & [Rep FName] and then go to the properties dialog to the Events tab, click on the ellipsis (...) and open the VBA Window. Then put in this code:
    Code:
    Me.HeaderNameHere.Visible = (Me.Status = "Active")
    And in the On Format Event of the DETAIL SECTION, put the same type of code:
    Code:
    Me.Detail.Visible = (Me.Status = "Active")

  3. #3
    cbrsix is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    214
    I can't seem to get it working. Here are some shots of what I have going on. Do I have to get rid of the Group Expression at the bottom?

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    I see in your first example that STATUS is a field in your report's recordset. It must be since you have IIF([STATUS]="Active"...

    So, you would use

    Me!Status

    to refer to it and not what you have in your code there (use the bang to ensure using the field name and make sure that there is no control on the report with the name of Status. If there is change it to txtStatus)

  5. #5
    cbrsix is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    214
    Thanks. It worked this time.

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

Similar Threads

  1. Sort Calculated Ages in to Age Groups
    By l3111 in forum Queries
    Replies: 2
    Last Post: 06-09-2011, 04:33 AM
  2. Carrying over sort to report
    By eww in forum Programming
    Replies: 2
    Last Post: 03-04-2011, 03:39 PM
  3. report grouping and sort
    By Meccer in forum Access
    Replies: 1
    Last Post: 01-05-2011, 08:30 AM
  4. sort data in the report
    By rawandjamal in forum Reports
    Replies: 1
    Last Post: 12-23-2010, 07:07 PM
  5. Report Based on Form - Sort
    By jeffyyy in forum Reports
    Replies: 1
    Last Post: 10-16-2010, 06:15 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
  •  
Other Forums: Microsoft Office Forums