Report Filtering 1

[Herdrich]

Im using some code that i found online that the part they supplied works with filtering the dates but im also trying to patch in filtering the Type field in my database but it dosent work. Can you show me what im doing wrong?

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: [URL unfurl="true"]http://allenbrowne.com/casu-08.html[/URL]
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDataType As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
   
    'DO set the values in the next 3 lines.
    strReport = "Log"      'Put your report name in these quotes.
    strDateField = "[TimeOut]" 'Put your field name in the square brackets in these quotes.
    strDataType = "[Type]"
    lngView = acViewPreview
    
    ' This is what i added
    If Not IsNull(Me.Combo39) Then 'Type combo box.
        strWhere = strWhere & "([strDataType] = " & Me.Combo39 & ") AND"
    End If
    '^^^^^^^^^^^^^^^^^^^
    '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      
        
        
        'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If     
    
    
    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    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

 

[PHV]

Replace this:
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ") "
with this:
strWhere = strWhere & " (" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ") "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Herdrich]

Now i get an input box for strDataType and the data type i selected if its a one word type and a syntax error if its a two word type.

missing operator in query expression '(strDataType = Active Duty) AND([TimeOut] >= #10/03/2010#) AND ([TimeOut] < #10/04/2010#)

 

[PHV]

strWhere = strWhere & "([strDataType] = [!]'[/!]" & Me.Combo39 & "[!]'[/!]) AND"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Herdrich]

Amazing Thank You!