search form for date range

[sharpnote]

I came across a sample code for multiple field search form (please see below). One of the variables is the age. I wanted to replace it with date so that I can do search for particular date range. How can I modify the codes below. Thanks a lot


Private Sub btnClear_Click()
Dim intIndex As Integer

Me.txtMaxAge = ""
Me.txtMinAge = ""

End Sub
. . . . . . . . .

Check for min Age
' If Me.txtMinAge > "" Then
' varWhere = varWhere & "[Age] > " & Me.txtMinAge & " AND "
' End If

' Check for max Age
' If Me.txtMaxAge > "" Then
' varWhere = varWhere & "[Age] < " & Me.txtMaxAge & " AND "
' End If

 

[pbaldy]

I'd test the controls with IsDate(). Further, date values need to be surrounded by #, like the third example here:

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

 

[sharpnote]

Thank you pbaldy for the quick response. I followed the baldyweb sample and I got this highlighted error. I check record source and my query, it seems everything is in order. What gives? thanks

' Update the record source
Me.frmsubqrypolicydata.Form.RecordSource = "SELECT * FROM qrypolicydata " & BuildFilter

 

[pbaldy]

What error? You can use this technique to make sure the SQL is coming out correctly:

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

 

[sharpnote]

The error message was
"Run time error 3075':
syntax error in string query expression [fieldName] < # date#


This was highlighted in yellow, when I debug the code.

' Update the record source
Me.frmsubqrypolicydata.Form.RecordSource = "SELECT * FROM qrypolicydata " & BuildFilter

 

[pbaldy]

If you have the actual word date in the error, you aren't getting a date value. Hard to fix without seeing the code that creates the string.

 

[sharpnote]

In my first post, the code was for the age, following your sample code for dates, I replace the age with date and this is what I made

' Check for before date
If Me.txtbeforedate > "" Then
varWhere = varWhere & "policy_date > #" & Me.txtbeforedate & "#"" And "
End If

' Check for after date
' If Me.txtafterdate > "" Then
' varWhere = varWhere & "policy_date < #" & Me.txtafterdate & "#"" And "
' End If

 

[sharpnote]

This is what I got, after replacing the age with date

' Check for before date
If Me.txtbeforedate > "" Then
varWhere = varWhere & "policy_date > #" & Me.txtbeforedate & "#"" And "
End If

' Check for after date
' If Me.txtafterdate > "" Then
' varWhere = varWhere & "policy_date < #" & Me.txtafterdate & "#"" And "
' End If

 

[pbaldy]

Try

varWhere = varWhere & "policy_date > #" & Me.txtbeforedate & "# And "

 

[sharpnote]

Great!... it's working. Thanks a lot pbaldy!!!!

 

[pbaldy]

Happy to help!