issues with filtering with combo box's and dates

[sspreyer]

hi All
the code I have below have got it working but I have one problem if I leave the txtstartdate and txtenddate empty "NO DATES ENTERED" it will show all
records which is great. but if I fill client name in combo box name= cboclient and leave
txtstartdate and txtenddate emtpy it will show only record's with client name
entered in Combo box name = cboclient which yet again is great but if I fill client name in
combo box name = cboclient and a client name in combox box name = 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 entered really weird one!!

the red code is my filter

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

any help !!!! would be much appreciated!!!!


cheer

shane

[June7]

Have you step debugged to see what is actually happening? Follow the code as it executes to find where it deviates from expected behavior.

When mixing AND and OR operators, parenthesizing becomes critical.

BETWEEN startdate AND enddate AND (customer1 OR customer2)

will give different results from

BETWEEN startdate AND enddate AND customer1 OR customer2

[sspreyer]

hi june7

im having issues debugging as the code behind the form not a module I cant seem to step though it but if put it in module it doesn't like the me Keyword. so struggling to test it. my vba knowleage is very little sorry if im being thick


thanks for your help

shane

[June7]

Debugging form code is same as general module code. I use step all the time.

[sspreyer]

hi june7 can't see what I'm doing wrong I have selected breakpoint and then F8 just get beep nothing happen but works if code in module but doesn't like "me. keyword" so I cant step the bit I want .

shane

[sspreyer]

hi June7

I found the problem it was due to my report being run by query changed to my table works fine

cheer

shane