Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've tried to tackle this problem a few different ways with several queries, including a cross-tab query, but I'm stuck and am hoping someone can help.

    As you may have seen in another thread, I needed to put hours worked in Days-of-the-Week Fields, so I have a Monday, Tuesday, Wednesday, etc Field into which hours worked are entered.

    I need to be able to show staff whose hours are less than 36.5. At the moment, I have a Report that Groups by Staff Name and then Sums each days hours to display the Total Hours worked for the week (a User will enter the Week Commencing from a Parameter.

    For example, John Smith who has worked on a Total of 2 projects for a total of 37 hours. The next Group is a staff member who has worked on 1 Project for only 20 hours. How can I get Access to hide John Smith's Record in the Report and only show the staff member who has completed less than 36.5 hours this week?

    I hope I've explained this well.

    Thanks for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does your report display only the group header and/or footer, or also the detail records?

  3. #3
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='798003' date='14-Oct-2009 22:01']Does your report display only the group header and/or footer, or also the detail records?[/quote]

    Thanks for replying, Hans.



    Only the Group By Field's Header & Footer are shown. The Detail is not shown. The [txtTotalHours] Field needs to be evaluated for each Record. If this Field, which totals the Hours for all the Projects for all the Days < 36.5, then that Staff Member's Name & Hours Record should visible; if = or >, then it should be hidden.

    Thanks, as always.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Create On Format event procedures for the group header and footer:

    Code:
    Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
      Cancel = (Me.txtTotalHours >= 36.5)
    End Sub
    
    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
      Cancel = (Me.txtTotalHours >= 36.5)
    End Sub
    Substitute the actual names of the group header and footer.

  5. #5
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans. I tried that, but all the records still appear. I know you're always right, so I'm assuming I've misinterpreted your instructions.

    I opened the Report and selected the StaffName Header. I then opened the Properties and put the Code in the On Format event.

    I then clicked the StaffName Footer and put the Code in that On Format Event Procedure, so it looks like this:

    Code:
    Option Compare Database
    
    Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = (Me.txtTotalHours >= 36.5)
    End Sub
    
    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = (Me.txtTotalHours >= 36.5)
    End Sub
    But when I opened the Report, it displayed both the Records, as per the screen-cap above, ie, 1st Staff Member 37.5 hours and the second staff member at 35 hours.

    Thanks for any clarification you can provide.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm definitely not always right!

    Does it make a difference if you copy the text boxes from the group footer into the group header (you can set the Visible property of the copies to No) and let the code refer to the total hours text box in the group header?

  7. #7
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well, I don't recall your ever being wrong. I don't know what I'd do without your knowledge!

    Anyway, I did try copying the contents of the Footer into the contents of the Header, but it all still appears, except now it's twice, LOL!

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='GreenLantern' post='798265' date='15-Oct-2009 23:58']Well, I don't recall your ever being wrong. I don't know what I'd do without your knowledge!

    Anyway, I did try copying the contents of the Footer into the contents of the Header, but it all still appears, except now it's twice, LOL![/quote]

    Try something like this in the Group Footer
    Put The totals in the footer just like it was originally


    Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
    If txtTotalHours >= 36.5 Then
    GroupHeader0.Visible = False
    GroupFooter1.Visible = False
    Else
    GroupHeader0.Visible = True
    GroupFooter1.Visible = True
    End If

    End Sub


    It worked for me

    Incidentally, Hans Method also works for me just as well.

    I assume that the Week Total Hours Box
    which is =Sum(Monday) + Sum(Tuesday) + etc is Called txtTotalHours on your Report

    If Not It would be an undefined or zero value and always less than 37.5
    Andrew

  9. #9
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Andrew, but I still get all records.

    BTW, txtTotalHours =[SumMonday]+[SumTuesday]+[SumWed]+[SumThursday]+[SumFriday]

    Is that what you meant?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The solution I proposed does work in my test database, so I think we'd need to see a stripped down copy of the database to see why it does not for you.

    • Make a copy of the database (in Access 2000 format) and work with that.
    • Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    • In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    • Remove or modify data of a confidential nature.
    • Perform a compact and repair (Tools/Database Utilities).
    • Make a zip file containing the database.
    • Attach the zip file to a reply.

  11. #11
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='GreenLantern' post='798278' date='16-Oct-2009 01:36']Thanks Andrew, but I still get all records.

    BTW, txtTotalHours =[SumMonday]+[SumTuesday]+[SumWed]+[SumThursday]+[SumFriday]

    Is that what you meant?[/quote]

    Yes. I did not know your exact field names.
    It was to make sure that the total hours field was called txtTotalHours

    I agree with Hans that next step would be to see a paired down copy of the actual database,
    because both methods to hide section are functional,
    so there must be something we cannot see.
    Andrew

  12. #12
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    Thanks guys. DB attached.
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The version that you posted doesn't quite work for me, but it does if I use the code I proposed:

    Code:
    Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
      Cancel = (Me.txtTotalHours >= 36.5)
    End Sub
    
    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
      Cancel = (Me.txtTotalHours >= 36.5)
    End Sub
    See screenshot:

    [attachment=86026:x.jpg]
    Attached Images Attached Images
    • File Type: jpg x.jpg (32.6 KB, 1 views)

  14. #14
    New Lounger
    Join Date
    Sep 2009
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy

    Crud. Must be a bug in Access 2007, or Microsoft has changed the way it works in 2007.

    I tried it both at home and here today in work (both are 2007), and in both versions, it didn't work.

    Unless I've been really stupid. Can you upload the version you modified so I can see if it works here in 2007?

    If not, do you think there'd be a way to do this in a Query so we wouldn't have to reply on VB? I tried a cross-tab Query, but I then can't enter Criteria for 'Week Commencing'. Is there another way to do it that I'm not aware of or haven't thought of, or will I just have to say there's a bug in 2007 that precludes the VBA from working?

    Thanks for all your help so far.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't have Access 2007, so I can't comment on that.

    I have attached a zip file with the database. Apart from the report that works for me in Access 2002, I have added a second query and report where the weekly sums are calculated in the query, so that the condition <36.5 can be imposed in the query. The group header and footer have been removed, and the detail section has been made visible.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •