Allen Browns Date Range + Combo Box for Client and Project Report Filter Coding Help

[bbarclay]

Date range works great even after adding my code. But I still can't get the combo boxes labeled "txtClient" to filter out the Client section of the report based on the criteria. // I've added this code //

  If Not IsNull(Me.txtClient) Then
        strWhere = strWhere & "([Client] = " & Me.txtClient & ") AND "
    End If

But still can't get it to work. Any suggestions please.

Here is the full code I'm using for this process.

Thanks for any help

Brandon

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: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport 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 = "Payment_Due_Date"      'Put your report name in these quotes.
    strDateField = "[Payment_Due_Date]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
    
    
    'Build Combo Filter
    
        If Not IsNull(Me.txtClient) Then
        strWhere = strWhere & "([Client] = " & Me.txtClient & ") 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

[pbaldy]

What does the Debug.Print line result in? What is the data type of client?

[bbarclay]

Doesn't return anything just goes to the report but doesn't filter. The datatype is going to be text. Though i added this code to my report and it's returning a number, which I believe is the ID from the combobox. =[Forms].[frmWhatDates].[txtClient] // That will return a number instead of George, Frank or Tim.

[bbarclay]

// More info// The field that is in the report is SELECT [Client].[ID], [Client].[Client], Client.[project_lookup] FROM Client; // The combo box is this. SELECT Client.ID, Client.Client FROM Client; // So it's trying to pass the id to filter, but being that the report field is by ID i think that it's not trying to pass text but a key value like.... 1, 2, 3, 4. Then it would display the name.

[bbarclay]

// if i do a seperate button. and do the following code, it will filter out client.

Private Sub Command13_Click()
DoCmd.OpenReport "Payment_Due_Date", acPreview, , " [Client] = " & Me.[txtClient]
End Sub

[pbaldy]

What I meant was to uncomment this line:


Debug.Print strWhere

and post here what gets output to the Immediate window. That will tell us what the final string is, and we can compare it to what you expect.

[bbarclay]

K, i tried this and nothing pops up. Shouldn't there be a popu that tells me what it's for. That's not happening.

[pbaldy]

It goes to the VBA Immediate window:

http://www.baldyweb.com/ImmediateWindow.htm

[bbarclay]

([Payment_Due_Date] >= #01/02/2012#) AND ([Payment_Due_Date] < #09/07/2012#)

[bbarclay]

Fixed it. Here's what i did. I put that code at the bottom added AND to the date code like this.

    '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) & ") AND"
    End If
    
   If Not IsNull(Me.txtClient) Then
        strWhere = strWhere & "([Client] = " & Me.[txtClient] & ")"
    End If

[bbarclay]

Thank you for you help. Using Immediate window helped me to realize that my strWhere was not passing through the client information.

[pbaldy]

Happy to help.

[ARickert]

[QUOTE=bbarclay;117878]

If Not IsNull(Me.txtClient) Then
strWhere = strWhere & "([Client] = " & Me.[txtClient] & ")"
End If

I am having the same problem getting a combo box to function as another alternate filter to my report. you said you had fixed it by added the above to the end of your SLQ, but this isn't working for me, and I'm not sure what I'm doing wrong. Can you eleaborate? When I put that in for mine I go to debugging.

[pbaldy]

What is your code?

[ARickert]

Private Sub Command29_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: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport 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 = "DQC"      'Put your report name in these quotes.
    strDateField = "[Date]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.Text22) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.Text22, strcJetDate) & ")"
    End If
    If IsDate(Me.Text24) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.Text24 + 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
Private Sub RUN_Click()
End Sub

I want to add in that if a PEBLO is chosen from Combo27 that it ALSO filters through there. But whenever I add AND" to the end of this code I get a debug for this line: DoCmd.OpenReport strReport, lngView, , strWhere