how to add another combo box to filter a field

[sspreyer]

hi all

I have some code in which I enter a startdate and a enddate into textbox's and a combo box where I enter a client name when I run the code it will show all record's generated with in the date range and same client name set in the combo box "cboclient" what I would like to be able to do is have another combo box call cboclient2 which will allow me to enter another client name so then when the report opens it will show me with in the date range set in startdate and enddate and filter the client field by cboclient and cboclient2 so for e.g.

startdate
01/01/14

enddate
11/01/14

cboclient
peter

cboclient2
steve

run code show all record in date range and with client name peter and steve

hope you all understand what I'm trying to do


here's the code I have

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.

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 = "Input Report" 'Put your report name in these quotes.
strDateField = "[Date 1]" 'Put your field name in the square brackets in these quotes.
lngView = acViewReport 'Use acViewNormal to print instead of preview.

'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

[COLOR=red]If Len(Trim(Me.cboclient)) > 0 Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(Client = '" & Me.cboclient & "')"[/COLOR]
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

thanks in advance my vba knowledge is very little

shane

 

[pr2-eugin]

Your code indent is not making it easy to understand. However, You would need an Or operator..

(Date BETWEEN #01/01/2013# AND #01/01/2014#) AND (Name = 'Peter'[COLOR=Red][B] Or[/B][/COLOR] Name = 'Steve')

Be careful with the parentheses.

 

[sspreyer]

Sorry that is not what I mean basically I would like to be able to filter client field twice by cboxcleint and cbocleint2 combo boxes the red code in my first post shows cbocleint and code works perfect but don't no how to add cbocleint2 I have try different Scenario's but does work my vba knowledge is very little



Thanks

Shane

 

[TJPoorman]

Add this line after the code in red:

If Len(Trim(Me.cboclient2)) > 0 Then
    If strWhere <> vbNullString Then
        strWhere = strWhere & " OR "
    End If
    strWhere = strWhere & "(Client = '" & Me.cboclient2 & "')"
End If

 

[TJPoorman]

Also, you can clean your code up a bit something like the following:

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.

Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.

'Build the filter string.
If Nz(Me.txtStartDate,"")<>"" Then strWhere = "([Date 1] >= " & Format(Me.txtStartDate, strcJetDate) & ") AND "
If Nz(Me.txtEndDate)<>"" Then strWhere = strWhere & "([Date 1] < " & Format(Me.txtEndDate + 1, strcJetDate) & ") AND "
If Nz(Me.cboclient,0) > 0 Then strWhere = strWhere & "(Client = '" & Me.cboclient & "') AND "
If Nz(Me.cboclient2,0) > 0 Then strWhere = strWhere & "(Client = '" & Me.cboclient2 & "') AND "

If Len(strWhere)-5 <= 0 Then
    MsgBox "Nothing to do"
Else
    strWhere = Left(strWhere,Len(strWhere)-5)
End If

'Close the report if already open: otherwise it won't filter properly.
If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, "Input Report"

'Open the report.
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
DoCmd.OpenReport "Input Report", acViewReport, , strWhere

Exit_Handler:
    Exit Sub

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

 

[sspreyer]

hi thanks TJPoorman code worked great just one problem I have if I leave the txtstartdate and txtenddate empty it will show all records which is great. but if I fill client name in cboclient and leave txtstartdate and txtenddate emtpy it will show only record's with client name entered in cboclient which yet again is great but if I fill client name in cboclient and a client name in cboclient2 and leave txtstartdate and txtenddate emtpy it show's all record's with client name form cboclient but not cboclient2 but work's if I enter a date in txtstartdate and txtenddate it then show both clients enter in txtstartdate and txtenddate really weird one!!

here the code

 Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it 
    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.
        'Put your report name in these quotes.
        strReport = "Input Report"
  If Me.Check10 = True Then
   strDateField = "[Date 1]" 'Put your field name in the square brackets in these quotes
    ElseIf Me.Check10 = False Then
    strDateField = "[Date Work Completed]"
    End If
     lngView = acViewReport     'Use acViewNormal to print instead of preview.
    
    '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 Len(Trim(Me.cboclient)) > 0 Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(Client = '" & Me.cboclient & "')"
End If
 If Len(Trim(Me.cboclient2)) > 0 Then
    If strWhere <> vbNullString Then
        strWhere = strWhere & " OR "
    End If
    strWhere = strWhere & "(Client = '" & Me.cboclient2 & "')"
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

thanks for your time

shane

 

[sspreyer]

hi all

some one please help!!!!
shane

 

[sspreyer]

hi all

have sorted it due to running report from query cause problem

thanks

shane