Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Unanswered: Report Filtering Question

    Hello,

    I am working on a project in which I have a table with data that I would like to generate reports off of. I currently have code to filter by date ranges but I need to add to it so I can filter further by another column in the table. Here is the code I currently have setup for the data filtering. Any help would be appreciated.

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

    strReport = "rptdtrpt"
    strDateField = "[dt_Date]"
    strasset = "[dt_asset]"
    lngView = acViewPreview

    'Build the filter string.
    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

    'Open the report.
    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. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Wouldn't be hard; you just add the appropriate code to keep building strWhere.
    Paul

  3. #3
    Join Date
    Sep 2011
    Posts
    4
    I added the strWhere code and was able to get the additional filtering to work. Now when I leave the new combo box empty, no records show on the report. IS there a way to disable the combo box filtering when the it's blank or can I add a checkbox to enable/disable the combo box?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It would help to see your code. Basically, you'd do it the same way you did the other. You only add to the string if the combo is populated.
    Paul

  5. #5
    Join Date
    Sep 2011
    Posts
    4
    Private Sub cmdPreview_Click()
    On Error GoTo Err_Handler
    Dim strReport As String
    Dim strDateField As String
    Dim strasset As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"

    strReport = "rptdtrpt"
    strDateField = "[dt_Date]"
    strasset = "[dt_asset]"
    lngView = acViewPreview

    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
    strWhere = "[dt_asset] = '" & Me.Asset_list & "'"

    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

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Like I said, test the combo's contents before adding to the string. Just like you did for the dates.
    Paul

  7. #7
    Join Date
    Sep 2011
    Posts
    4
    I am just starting with writing vb, I did not create this code, I do not know where to test the string. Can you offer some assistance?

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I figured that. If I just do it for you, you'll be in the same boat next time you need something. Why don't you learn now? Figure out what each line of that code is doing, and understand why. Each line starting with "If" is testing something. You need to test the combo. A generic test is:

    If Len(Me.SomeControl & vbNullString) = 0 Then

    which will simply test that something is there.
    Paul

Tags for this Thread

Posting Permissions

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