Building A Filter

[PC User]

I have a filter on a form for my reports and it consists of: 6 comboboxes and 2 date fields. The user may use one or all the filters for reports of the same recordsource. The reports are of two catagories indicated by their name's prefix "rwo" or "rpg". One of the filter comboboxes (Filter5) has to change its rowsource when switching between report catagories. I'm having trouble building the filter and getting it to work. The date filter is not being included in the filter and Filter5 is not working. Please help.

Private Sub btnSetFilter_Click()
On Error Resume Next

Dim strSelect As String, strFrom As String
Dim strSQL As String, strWhere As String
Dim intCounter As Integer, strRowSource As String
Dim strDate As String

     'Build SQL String *****
        If Left(gstrReport, 3) = "rwo" Then
           strDate = "ActualStartDate"             'Work Order Start Dates
                strSelect = "SELECT DISTINCT tblMainData.ActionDescription "
                strFrom = "FROM tblMainData "
                strWhere = "ORDER BY tblMainData.ActionDescription;"
                strRowSource = strSelect & strFrom & strWhere
        ElseIf Left(gstrReport, 3) = "rpg" Then
           strDate = "DueDate"                     'Program Due Dates
                strSelect = "SELECT DISTINCT tsubProgramList.ProgramDescription "
                strFrom = "FROM tsubProgramList "
                strWhere = "ORDER BY tsubProgramList.ProgramDescription;"
                strRowSource = strSelect & strFrom & strWhere
        End If
                Me!Filter5.RowSource = strRowSource
    
     'Date Filter
        If Not IsNull(IsDate(BeginningDate)) And Not IsNull(IsDate(EndingDate)) Then
            If EndingDate < BeginningDate Then
                MsgBox "The ending date must be later than the beginning date."
            End If
        Else
            strSQL = "([CDate(strDate)] Between #" & Me.BeginningDate & "# And #" & Me.EndingDate & "#) And "
        End If
             
     'Combobox Filter
        For intCounter = 1 To 6
          If Me("Filter" & intCounter) <> "" Then
            strSQL = strSQL & "[" & Me("Filter" & intCounter).tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
          End If
        Next

     If strSQL <> "" Then
        'Strip Last " And "
        strSQL = Left(strSQL, (Len(strSQL) - 5))
        'Set the Filter property
        gstrFilter = Nz(strSQL, "")
     
     Else
        gstrFilter = ""
     End If
End Sub

Thanks,
PC

 

[izyrider]

add a temporary line:

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
Debug.Print strSQL
'Set the Filter property
gstrFilter = Nz(strSQL, "")

then ctrl-G and copy/paste strSQL on the site if the error is not immediately obvious to you from the debug dump

izy

 

[PC User]

Thanks for your reply. The problem seems to be capturing the date criteria. I made some changes to try to simplify the changing of the combobox recordsource and the changing of the date field. The reports are displayed in two listboxes. The work order listbox code is:

Private Sub lstWorkOrderReports_Click()
       gstrReport = ""                                 'Global variable
        gstrDate = "ActualStartDate"             'Work Order Start Dates
        strSelect = "SELECT DISTINCT tblMainData.ActionDescription "
        strFrom = "FROM tblMainData "
        strWhere = "ORDER BY tblMainData.ActionDescription;"
        gstrRowSource = strSelect & strFrom & strWhere
        Me!Filter5.RowSource = ""
        Me!Filter5.RowSource = gstrRowSource
    Me!Selected = Me!lstWorkOrderReports.Column(0)
    gstrReport = Me!lstWorkOrderReports.Column(0)
    'gstrReport = Me!Selected
    Me!txtReportDesc = ReportDescription(Me!lstWorkOrderReports)
End Sub

The Program listbox code is:

Private Sub lstProgramReports_Click()
        gstrReport = ""                                 'Global variable
        gstrDate = "DueDate"                     'Program Due Dates
        strSelect = "SELECT DISTINCT tsubProgramList.ProgramDescription "
        strFrom = "FROM tsubProgramList "
        strWhere = "ORDER BY tsubProgramList.ProgramDescription;"
        gstrRowSource = strSelect & strFrom & strWhere
        Me!Filter5.RowSource = ""
        Me!Filter5.RowSource = gstrRowSource
    Me!Selected = Me!lstProgramReports.Column(0)
    gstrReport = Me!lstProgramReports.Column(0)
    'gstrReport = Me!Selected
    Me!txtReportDesc = ReportDescription(Me!lstProgramReports)
End Sub

And the revised code to build the filter is:

Private Sub btnSetFilter_Click()
On Error Resume Next

Dim strSelect As String, strFrom As String
Dim strSQL As String, strWhere As String
Dim intCounter As Integer, strRowSource As String
Dim strDate As String, strFilter As String
     
     'Build SQL String *****
     'Date Filter
        If Not IsNull(IsDate(BeginningDate)) And Not IsNull(IsDate(EndingDate)) Then
            If EndingDate < BeginningDate Then
                MsgBox "The ending date must be later than the beginning date."
            End If
        Else
            strSQL = "[gstrDate] Between #" & Me.BeginningDate & "# And #" & Me.EndingDate & "#) And "
        End If
             
     'Combobox Filter
        For intCounter = 1 To 6
          If Me("Filter" & intCounter) <> "" Then
            strSQL = strSQL & "[" & Me("Filter" & intCounter).tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
          End If
        Next

     If strSQL <> "" Then
        'Strip Last " And "
        strSQL = Left(strSQL, (Len(strSQL) - 5))
        Debug.Print strSQL
        'Set the Filter property
        strFilter = Nz(strSQL, "")
        gstrFilter = Nz(strSQL, "")
     Else
        gstrFilter = ""
     End If
End Sub

Problem 1:
I've used other global variables in the my application, but this is the first time I'm trying to capture a field name that handles a date. The filter doesn't pick up any date information.
Problem 2:
Also the combobox "Filter5" has to change recordsource, because the report listboxes have lists of reports that have data from two different tables. It's like having two catagories of reports. I get an error when toggling between recordsources that there's a problem with a key field in a relationship. Do you have any idea for solutions to these two problems?

Thanks,
PC