Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting 0 records in a report

    For one report I am doing I need to count the number of records on the report and I need to show "0" if there are no records. the standard Count( ) method does not work with zero records. I have not been able to come up with a satisfactory approach.

    I need to count the field "LNFN"

    The no Data Event is not appropriate for what I need to do.

    Any suggestions?

    Thanks.

    Paul

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by pbien View Post
    The no Data Event is not appropriate for what I need to do.l
    Paul,

    What do you need to do? If we know what you are trying to accomplish it would be a great help in coming up with a solution.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a subreport that is listing the participants (Lnfn) in a particular activity. I am trying to count the number of Participants in that activity. Once I get the list and the count I am going to post the subreport into another report.

    See attachment.
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Paul,

    I took a look at your table and I think you need to reorganize it into a set of tables, e.g. Students, Classes, etc., then use the Join capability of Access to tie them together to get the information. I'd recommend a beginner's book on Access table design to get you started.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The issue is not quite what it seems here. The problem is that Access automatically hides a subreport when it has no data. So even though the subreport itself correctly counts the number of students as 0, you don't see that on the main report.

    A workaround is needed. One option is to add some additional Labels to the main report:

    e.g. "July 28 No Students", which I have callled LabelJuly28

    then put this code in the OnFormat event for the Detail section. Repeat for each subreport.

    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
        If Me.subrptJuly28.Report.HasData = True Then
            Me.LabelJuly28.Visible = False
        Else
            Me.LabelJuly28.Visible = True
        End If
    End Sub
    Regards
    John



  6. #6
    2 Star Lounger
    Join Date
    Apr 2008
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you John. Once again your solution works.

    In reply to Retired Geek. I realize my table layout is not as clean as it could be. I tried using multiople tables but ran into issues that I did not have time to figure out. I had a short time table to get this done. I'm learning. Each database I do is better than the last one.

    Thanks to both of you for all your help the last two weeks.

    Paul

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Paul,

    I know how that is. We had a saying where I worked: "There is never enough time to do it right...but there is always time to do it over!"
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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