hi,
all
I have a sub form that I enter date range in 2 text box's txtstartdate and txtenddate this is filtering field "date raised" and I have combo box call cboclient this is filtering field "client name" this code is behind a command button after hitting the command button it open's the report and it show's all records with in the date range and with client name enter in cboclient
works great
problem is I have now added another cboclient2 to filter client name twice
so now I enter a date range in txtstartdate and txtenddate put a client name in cboclient and a client name in cbocleint2 hit command button it open's the report
but doesn't apply the date range to cboclient2 it just show's all records with that client name
here's the code
I suspect the problem is to do with "or" in my statement for cboclient2
I have try "AND" get a syntax error
im struck on what to try
any help much appreciated
cheers
shane
all
I have a sub form that I enter date range in 2 text box's txtstartdate and txtenddate this is filtering field "date raised" and I have combo box call cboclient this is filtering field "client name" this code is behind a command button after hitting the command button it open's the report and it show's all records with in the date range and with client name enter in cboclient
works great
problem is I have now added another cboclient2 to filter client name twice
so now I enter a date range in txtstartdate and txtenddate put a client name in cboclient and a client name in cbocleint2 hit command button it open's the report
but doesn't apply the date range to cboclient2 it just show's all records with that client name
here's the code
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 raised]" '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
[COLOR=red] If Len(Trim(Me.cboclient2)) > 0 Then
If strWhere <> vbNullString Then
strWhere = strWhere & [COLOR=blue]" OR "
[/COLOR] End If
strWhere = strWhere & "(Client = '" & Me.cboclient2 & "')"[/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
I suspect the problem is to do with "or" in my statement for cboclient2
I have try "AND" get a syntax error
im struck on what to try
any help much appreciated
cheers
shane