Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Filtering Report (2003)

    I have a report that I would like to use repetively using a filter, instead of creating multiple reports with multiple queries. However, I am having a difficult time understanding the Filter property of a report using vba. In a nutshell, I want to read a profit center from a table, and looping through the profit Centers one at time, apply the selected Profit Center as a filter in the report.

    Below is the code I am using to call the report and filter:
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim stDocName As String
    Dim strWhere As String



    stDocName = "rpt2008"
    ' Loop through the Profit Centers
    Set db = CurrentDb
    strSQL = "SELECT ProfitCenter " & _
    "FROM tblData2Corp " & _
    "GROUP BY ProfitCenter "

    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
    Do While Not rst.EOF
    strWhere = "[Hier1] = ' " & rst!ProfitCenter & " ' "
    Me.Filter = strWhere
    Me.FilterOn = True

    If Me.chkPreview = True Then
    DoCmd.OpenReport stDocName, acNormal, , strWhere
    Else
    DoCmd.OpenReport stDocName, acViewPreview, , strWhere
    End If
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing

    When running the code I get the following error message:
    "Syntax error (missing operator) in query expressing '[Hier1] = 'NameOfProfitCenter' )'.

    I am not sure which operator is missing.

    Thanks in advance for your ideas.

    Ken

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

    Re: Filtering Report (2003)

    I wonder where the closing parenthesis in the error message comes from. Are you sure that it's not in your code?

    Does it help if you change the line
    <code>
    strWhere = "[Hier1] = ' " & rst!ProfitCenter & " ' "
    </code>
    to
    <code>
    strWhere = "[Hier1] = " & Chr(34) & rst!ProfitCenter & Chr(34)</code>

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Filtering Report (2003)

    Hans,

    THAT DID IT! I thought I could get away with a single quote. I need to remember to try the "Chr(34)" when I have these issues, as this is not the first time I had to revert to it.

    THANKS! ! !

    Ken

Posting Permissions

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