Hi. I have a table. In the table, a field has dates. I make a query with that field. I set up a filter macro that returns results where dates are between Date1 and Date2. My dates are mm/dd/yyyy. In my query, I use CVDate function as an expression for the date field. The reason why is because if I don't do that, the data exported to my excel spreadsheet would not be formatted as date. I have another macro that exports the query to excel. When I run my macro to filter, it gives me error:
Error - 3464: Data type mismatch in criteria expression
Note that if I do not use the CVDate function in my query expression, I don't get this error but what gets returned is blank!
Code:
Public Sub cmdSearch_Click()
On Error GoTo errHandler
If IsNull (Me.txtDate1) = False Then
strFilter = strFilter & "[Date] >=#" & Me.txtDate1 & "# AND [Date] <=#" & Me.txtDate2 & "# AND"
End If
End If
If Right(strFilter, 5) = " AND " Then strFilter = Left(strFilter, Len(strFilter) - 5)
If strFilter <> "" Then
Me.sfmFindings.Form.Filter = strFilter
Me.sfmFindings.Form.FilterOn = True
Else
Me.sfmFindings.Form.Filter = ""
Me.sfmFindings.Form.FilterOn = False
End If
errExit:
Exit Sub
errHandler:
If Err.Number = 3075 Then
MsgBox "Please press Reset button before doing a new Search"
Resume errExit
Else
MsgBox "Error - " & Err.Number & ": " & Err.Description, vbCritical, "Fatal Error"
Resume errExit
End If
End Sub