Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting Filtered data into a report (2000)

    I have a Form based on a query. In the process of working with the data I apply various filters to the underlying data from my Form. On the Form I also have a cmd btn to print out a REPORT based on the new "filtered" dataset. I have assembled a report based on the original query so it only shows the full dataset. How can I get it to show the "filtered" version.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting Filtered data into a report (2000)

    The DoCmd.OpenReport method has a WhereCondition argument that can be used to pass a filter. In this case, you want to pass the filter that has been applied to the form. In the following code, the command button is called cmdReport, and the report is named rptMyReport. Replace these with the appropriate names. Here is the On Click event procedure for the command button:

    Private Sub cmdReport_Click()
    Dim strWhereCondition As String

    On Error GoTo ErrHandler

    If Me.FilterOn Then
    strWhere = Me.Filter
    End If

    DoCmd.OpenReport "rptMyReport",acViewPreview, , strWhereCondition
    Exit Sub

    ErrHandler:
    ' Ignore error 2501 = report canceled
    If Not (Err = 2501) Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting Filtered data into a report (2000)

    Hans,

    I went trolling thru the Forum looking for a method whereby I could preview a Report using filtered data. The following almost worked:

    Private Sub prvCNMstrAudit_Click()
    Dim strWhereCondition As String
    On Error GoTo ErrHandler
    If Me.FilterOn Then
    strWhere = Me.Filter
    End If
    DoCmd.OpenReport "rpt CNMstr - Audit", acViewPreview, , strWhereCondition
    Exit Sub
    ErrHandler:
    ' Ignore error 2501 = report canceled
    If Not (Err = 2501) Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    I got a "Variable Not Defined" Error message on the strWhere = Me.Filter line. I assumed this was because there was no definition for it so I stuck in:

    Dim strWhere As String

    and it compiled OK. It also ran the preview without the filter and showed all records. Unfortunately when I ran it after applying the filter, I still got all the records and not just the filtered one.

    Was it something I did,
    Cheers,
    Andy

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting Filtered data into a report (2000)

    I believe the line:

    strWhere = Me.Filter

    should be

    strWhereCondition = Me.Filter

    since you declared strWhere, it is storing Me.Filter as a string, however when you apply the arguments to docmd.openreport, you're referring to strWhereCondition, which contains nothing. Either change strWhereCondition to just strWhere or change strWhere to strWhereCondition.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting Filtered data into a report (2000)

    Thanks Jeremy,

    You were bang on! Appreciate it,
    Cheers,
    Andy

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Getting Filtered data into a report (2000)

    I also always declare an Option Explicit as part of all code so it catches all compile errors.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting Filtered data into a report (2000)

    Pat,

    I too, always have the Open Explicit, and in this case did catch the compile error. However, I wasn't sure exactly how to correct the error, ergo, multiple attempts,
    Cheers,
    Andy

Posting Permissions

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