type mismatch error 13

[Jon]

hi,

I am using Allen Browne advance filter code:

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#dd\/mm\/yyyy\#"

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([vacationdate] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([vacationdate] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND
" at the end.
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True

End If

but the msg "type mismatch error 13" shows & highlighted at the line:

If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([vacationdate] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If

Please help???

 

[Allen Browne]

Firstly, do not change the const to match your own regional settings. Leave
it the way JET expects the format, i.e.:
Const conJetDate = "\#mm\/dd\/yyyy\#"
Explanation:
http://allenbrowne.com/ser-36.html

Secondly, the code assumes that txtEndDate contains a value of type Date. It
won't work if it contains a non-date, such as a string.

Perhaps you cleared the date programmatically with:
Me.txtEndDate = ""
where you should have had:
Me.txtEndDate = Null

Provided you set the Format property of the text box to a date format (e.g.
General Date), you won't be able to enter a non-date value into the control,
so make sure you don't assign a non-date value to the unbound control.