date range in search criteria of form/w subform

[michael_folsom]

I recently added a date range to an existing search form, but I have been unable to get it to work, so I am enclosing the code. It only works if I put in the exact date that is in the database, and then only for either the start date (look_for_sdate) or end date (look_for_edate) but not when I populate both of those text fields on the form. Any suggestions would be helpful.
Thanks

Private Sub Search_records_stats_other_Click()
' Create a WHERE clause using search criteria entered by user and
' set RecordSource property of Search Subform.

Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant
Dim strdatefield As String
Dim strwhere As String
Const strcJetDate = "#\mm\dd\yyyy\#" 'Do not change it match your local settings

'Clear Select field
CurrentDb.Execute "UPDATE sample_table SET [Selected] = False;", dbFailOnError
' Initialize argument count.
ArgCount = 0
' Initialize SELECT statement.
MySQL = "SELECT * FROM master_query WHERE "
MyCriteria = ""
' Use values entered in text boxes in form header to create criteria for WHERE clause.
AddToWhere [Look For proname], "[contact_first_name]", MyCriteria, ArgCount
AddToWhere [Look For surname], "[contact_last_name]", MyCriteria, ArgCount
AddToWhere [Look For use], "[use_in]", MyCriteria, ArgCount
AddToWhere [Look For 50002], "[x50002]", MyCriteria, ArgCount
AddToWhere [Look for 50004], "[x50004]", MyCriteria, ArgCount
AddToWhere [Look for 50006], "[x50006]", MyCriteria, ArgCount
AddToWhere [Look for 50008], "[x50008]", MyCriteria, ArgCount
AddToWhere [Look for 50010], "[x50010]", MyCriteria, ArgCount
AddToWhere [Look for 50012], "[x50012]", MyCriteria, ArgCount
AddToWhere [Look for 50022], "[x50022]", MyCriteria, ArgCount
AddToWhere [Look for 50025], "[x50025]", MyCriteria, ArgCount
AddToWhere [look_for_begin], "[date_reported]", MyCriteria, ArgCount
AddToWhere [Look_for_end], "[date_reported]", MyCriteria, ArgCount

'Build the filter string
If IsDate(Me.look_for_begin) Then
strwhere = "(" & strdatefield & " >= " & Format(Me.look_for_begin, strcJetDate) & ")"
End If
If IsDate(Me.Look_for_end) Then
If strwhere <> vbNullString Then
strwhere = strwhere & " And "
End If
strwhere = strwhere & "(" & strdatefield & " < " & Format(Me.Look_for_end + 1, strcJetDate) & ")"
End If

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If
' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria
' Set RecordSource property of Search Subform.
Me![Search Subform Stats].Form.RecordSource = MyRecordSource

' If no records match criteria, display message.
' Move focus to Clear button.
If Me![Search Subform Stats].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "No records match the criteria you entered.", 48, "No Records Found"
Me!Clearall.SetFocus
Else
' Enable control in detail section.
Tmp = EnableControls("Detail", True)
' Move insertion point to Search Subform.
Me![Search Subform Stats].SetFocus
End If

End Sub