Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2006
    Posts
    14

    Unanswered: count records in a filtered report

    Hi. I have a search form that opens a report based on the whereclause in the search form:
    Input form code:

    'If only a County is selected, then whereclause will just be for records for county
    If Not IsNull(lstCounty) Then
    Whereclause = "[COUNTY] = '" & lstCounty & "'"
    End If

    'If County and Township is selected
    If Not IsNull(lstTownship) Then
    If Whereclause <> "" Then Whereclause = Whereclause & " AND "
    Whereclause = Whereclause & "[TWP] = '" & lstTownship & "'"
    End If

    'If County and Township and Section is selected
    If Not IsNull(lstSection) Then
    If Whereclause <> "" Then Whereclause = Whereclause & " AND "
    Whereclause = Whereclause & "[SECTION] = '" & lstSection & "'"
    End If


    DoCmd.OpenReport "St_Report", acViewPreview, , Whereclause

    Once the report opens, I would like to have a text box that says how many of the records, (after the whereclause has been implemented) are nonconfidential. Confidential (Y or N) is a field in the underlying query for the report. So, if a person chooses to view records for just a county I could have this in a textbox:
    #1.
    =DCount("[CONFIDENTIAL]", "Header", "[COUNTY] = '" & lstCounty & "' And [CONFIDENTIAL] = 'N'")

    #2.
    If they want to view records from a township then I would have this in a text box:
    =DCount("[CONFIDENTIAL]", "Header", "[COUNTY] = '" & lstCounty & "' And "[TWP] = '" & lstTownship & "' [CONFIDENTIAL] = 'N'")

    #3 would have Section included

    But instead of having multiple text boxes for every scenario, is there a way where I can say If only county is selected use the #1 Dcount, if county and township use #2 Dcount

    Hope this makes sense. Thanks

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If your parameter form is still open, you could reference that directly and use the desired formula:

    iif(nz(forms!yourParamForm!county, "") = "",
    DCount("[CONFIDENTIAL]", "Header", "[COUNTY] = '" & lstCounty & "' And [CONFIDENTIAL] = 'N'"),
    DCount("[CONFIDENTIAL]", "Header", "[COUNTY] = '" & lstCounty & "' And "[TWP] = '" & lstTownship & "' [CONFIDENTIAL] = 'N'"))
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2006
    Posts
    14
    Thank you for your response. I am not sure where I would put the code you sent. Would it go in the control source for the textbox or somewhere in the report event in vba. Thanks!

    Andrew

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    In the textbox on your report, wherever you're displaying it.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Perhaps I'm missing something, but wouldn't this be easier? A textbox in the appropriate footer (group, report or both):

    =Sum(IIf(Confidential = "N",1,0))
    Paul

  6. #6
    Join Date
    Feb 2006
    Posts
    14
    Thank you both. Yea Pbaldy, that sum works. Thank you! I didnt know it was that easy.

    Andrew

  7. #7
    Join Date
    Feb 2006
    Posts
    14
    Thank you both. Yea Pbaldy, that sum works. Thank you! I didnt know it was that easy.

    Andrew

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I seem to recall something about swatting at flies with a sledgehammer... oh yeah, that's 40% of my posts...

    *smacks forehead*
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Teddy, I knew you were going to whack your head when I posted that. I suspect this is one of those times when you get so focused on fixing what somebody posts you forget about the simpler ways. I know because I do it all the time myself, and end up whacking my own head.
    Paul

  10. #10
    Join Date
    Feb 2006
    Posts
    14
    I have another question if you guys know off hand. My report also has a subreport with fields like this for wells:
    Unit Depth Lithology
    1 0 soil
    2 20 shale
    3 33.5 coal
    4 37 limestone
    5 67 coal

    My subreport has a different underlying query to it. (There are tables that link the records to each other). Is there some way i can have a sum function in the report (*not subreport) header that gets the sum of records where a lithology is coal. So, for example, the above record will count as one. Since it has at least one coal unit. Thanks.

Posting Permissions

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