1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 VBA report filter

Discussion in 'Business Applications' started by Damien89, Apr 16, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Damien89

    Damien89 Thread Starter

    Joined:
    Apr 16, 2012
    Messages:
    5
    Hi All,

    I'm having difficult getting a VBA report filter to work.
    The code is based data within a form.

    I can get the data filter to work properly but I can't get the combo box part to work. I just get "Error 438"
    I could really use some help.

    Here's the code....


    Private Sub Preview_Click()
    On Error GoTo Err_Handler

    Dim strReport As String
    Dim strDateField As String
    Dim strFilterLevel As String
    Dim strWhere As String
    Dim varWhere As Variant
    Dim lngView As Long
    Const strcJetDate = "\#dd\/mm\/yyyy\#"

    strReport = "Members Detailed"
    strDateField = "[Member Entered]"
    strFilterLevel = "Account Level"
    lngView = acViewPreview

    If Me.cboFilterLevel > 0 Then
    cboFilterLevel = varWhere & "Account Level = " & Me.cboFilterLevel & "AND"
    End If

    If IsDate(Me.txtStartDate) Then
    strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
    If strWhere <> vbNullString Then
    strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If

    If CurrentProject.AllReports(strReport).IsLoaded Then
    DoCmd.Close acReport, strReport
    End If

    Debug.Print strWhere
    DoCmd.OpenReport strReport, lngView, , strWhere

    Exit_Handler:
    Exit Sub
    Err_Handler:
    If Err.Number <> 2501 Then
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If

    Resume Exit_Handler
    End Sub
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Damien89, welcome to the Forum.
    What is the Error Description?
    It would appear that the combo syntax is not correct as you have
    strWhere = strWhere
    for the other filters but
    cboFilterLevel = varWhere
    which would be trying to reset the combo if the combo is cboFilterLevel
     
  3. Damien89

    Damien89 Thread Starter

    Joined:
    Apr 16, 2012
    Messages:
    5
    The error description is:
    Error 438:Object doesn't support this property or method.

    The from fields are:

    Date from text box name = "txtStartDate"
    Date to text box name = "txtEndDate"
    Combo box name = "cboFilterLevel

    The report fields are:
    Account ID
    Member Name
    Account Level
    Member Entered

    How should I amend the syntax for the combo box?
    Thanks in advance - Damien.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    I would have thought it should be
    strWhere = strWhere & "Account Level = " & Me.cboFilterLevel & "AND"
    or
    strWhere = strWhere & "Account Level = '" & Me.cboFilterLevel & "'" & "AND"
     
  5. Damien89

    Damien89 Thread Starter

    Joined:
    Apr 16, 2012
    Messages:
    5
  6. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,189
    I have no idea what the VB is supposed to do except build a where clause for opening the report.It seems like a lot of rigamarole for setting the criteria for a date range and a level. cboFilterLevel is also not the name of your combo box, it is Combo18. You also need to declare the combo value as an integer, not a string. Some of the code looks like validation that ends up doing nothing. It also looks like you have a filter hard coded into the report. You will also never get the account level as a filter as there is no LevelID in the accounts table.
     
  7. Damien89

    Damien89 Thread Starter

    Joined:
    Apr 16, 2012
    Messages:
    5
    You're right, I've probably overcomplicated it. I'm a complete novice when it comes to visual basic and am trying to figure it out as I go.

    I'm trying to use a form to set a filter for a report. I need to filter the report within a date range and filter by Level ID as well.

    I have amended the Accounts table to have a 'Level ID' field and have corrected the combo box name.

    I can now generate the report and the date range filter applies, but the Level ID filter does not.
    Where am I going wrong? I really appreciate your help!

    Private Sub Preview_Click()
    On Error GoTo Err_Handler
    Dim strReport As String
    Dim strDateField As String
    Dim strLevelField As Integer
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#dd\/mm\/yyyy\#"

    strReport = "qryMemberAcqDate"
    strDateField = "[Member Entered]"
    strLevel = "[Level ID]"
    lngView = acViewPreview

    If IsNumeric(Me.cboFilterLevel) Then
    strLevelField = strLevel & "Level ID = " & (Me.cboFilterLevel) & """AND"
    End If

    If IsDate(Me.txtStartDate) Then
    strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
    If strWhere <> vbNullString Then
    strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If

    If CurrentProject.AllReports(strReport).IsLoaded Then
    DoCmd.Close acReport, strReport
    End If

    Debug.Print strWhere
    DoCmd.OpenReport strReport, lngView, , strWhere

    Exit_Handler:
    Exit Sub
    Err_Handler:
    If Err.Number <> 2501 Then
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If

    Resume Exit_Handler
    End Sub
     
  8. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,189
    Try this
    Dim strReport As String
    Dim strStartDate As String
    Dim strEndDate As String
    Dim strFilterLevel As String
    Dim strWhere As String
    Dim lngView As Long
    'Const strcJetDate = "\#dd\/mm\/yyyy\#" 'Do NOT change it to match your local settings.

    strReport = "Members Detailed"
    strStartDate = Me.txtStartDate
    strEndDate = Me.txtEndDate
    strFilterLevel = Me.Combo18
    lngView = acViewPreview

    If strFilterLevel > 0 Then
    strFilterLevel = "LevelID = " & Me.Combo18
    End If
    If IsDate(Me.txtStartDate) Then
    strWhere = "[Member Entered] >= #" & Me.txtStartDate & "#"
    End If
    If IsDate(Me.txtEndDate) Then
    If strWhere <> vbNullString Then
    strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & "[Member Entered] < #" & Me.txtEndDate & "# AND " & strFilterLevel
    End If

    If CurrentProject.AllReports(strReport).IsLoaded Then
    DoCmd.Close acReport, strReport
    End If

    Debug.Print strWhere
    DoCmd.OpenReport strReport, lngView, , strWhere
     
  9. Damien89

    Damien89 Thread Starter

    Joined:
    Apr 16, 2012
    Messages:
    5
    No Luck.

    I get the following error:

    Error 3075: Syntax error (missing operator) in query expression '[Member Entered]>=#1/02/2012# AND [Member Entered]<#18/04/2012# AND Level ID =1'.
     
  10. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,189
    You will need to add a LevelID field in the accounts table so it can be linked to the tblMemberLevel.(manually add the LevelID numbers that correspond to the descriptions) You will also need to modify the query used to create the report to include the LevelID field and also link to the tblMemberLevel table to bring in the Account Level name.
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1049539