Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006

    Unanswered: count filtered records in a report header

    Hi. I have a dialogue box that allows a user to filter by either county, mapid, or lat long degree limits. Then this opens a report with the appropriate records. Here is what I have:

    Private Sub cmdFind_Click()
    Dim Whereclause As String

    If Not IsNull(cboCounty) Then
    Whereclause = "[COUNTY_CODE] = '" & cboCounty.Column(1) & "'"
    End If

    If Not IsNull(txtMapID) Then
    Whereclause = "[MapIDENT] = '" & txtMapID & "'"
    End If

    If Not IsNull(txtMinX) Then
    Whereclause = "[X_LAM_NAD27] > " & txtMinX
    End If

    If Not IsNull(txtMaxX) Then
    If Whereclause <> "" Then Whereclause = Whereclause & " AND "
    Whereclause = Whereclause & "[X_LAM_NAD27] < " & txtMaxX
    End If

    If Not IsNull(txtMinY) Then
    If Whereclause <> "" Then Whereclause = Whereclause & " AND "
    Whereclause = Whereclause & "[Y_LAM_NAD27] > " & txtMinY
    End If

    If Not IsNull(txtMaxY) Then
    If Whereclause <> "" Then Whereclause = Whereclause & " AND "
    Whereclause = Whereclause & "[Y_LAM_NAD27] < " & txtMaxY
    End If

    'Open the report. If there are no records clear the 2501 runtime error
    On Error Resume Next
    DoCmd.OpenReport "rptStrat", acViewPreview, , Whereclause
    If Err = 2501 Then Err.Clear

    End Sub

    This all works fine. I am trying to put a text box on the report header that will count the number of filtered records from the whereclause. I did that with Count function and that works fine also. But, I am trying to then say how many of the filtered records are nonconfidential [CONFIDENTIAL] = N. Confidential is a field in a query (as county_code and mapIDENT are). So, Im kind of doing a filter of a filter. Im not sure what I need to add and how to word the Dcount function. Thanks for any help.


  2. #2
    Join Date
    Oct 2004
    Oxfordshire, UK
    If you wrap a dlookup or similar (returning the number of records you are seeking) in a function you can make the function the data source of the textbox. This would give you a report total record count.

    If you need a page by page count you'll have to open the recordset and move from record X to record Y (report page field values can be passed to function) testing each record against your criteria. Again return the result in a function that is the data source of the text box.

    For sections pass the section criteria to the function and return the result in a similar fashion.

Posting Permissions

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