Problem with dates

[Iwra]

I am using query by form to display a search from start and end date to find expiry dates for contracts so if the expiry date falls in between the selected date(S) list the results in a subfrm.

This all works very well however if I simply type a value of 03/05/05, it is not recognised even though the date exists in the table as an expiry date however If I type 03 May 05 then the result is returned. I have the format of my expiry date set to short date in my table.

I would like to include an input mask on the form also of short date - however when I do this I encounter a further problem with the code behind the search button on the QBF that displays a type mismatch error no 13. Taking the input mask out of the form and just typing 03 may 05 allows the code to run properly and display the correct results.

Here is the code I am using

If Not IsNull(Me![txtEndDate]) Then
where = where & " AND [Contract End Date] between #" + _
Me![txtStartDate] + "# AND #" & Me![txtEndDate] & "#"
Else
where = where & " AND [Contract End Date] >= #" + Me![txtStartDate] _
+ " #"
End If

 

[KenHigg]

If Not IsNull(Me![txtEndDate]) Then
where = where & " AND [Contract End Date] between #" & _
Me![txtStartDate] & "# AND #" & Me![txtEndDate] & "#"
Else
where = where & " AND [Contract End Date] >= #" & Me![txtStartDate] _
& " #"
End If


???
kh

 

[Iwra]

That's resolved the problem of the input mask. I can now have an input mask on the form and not get the type mismatch error.

However is is now returning incorrect records in the search

 

[Rich]

If Not IsNull(Me![txtEndDate]) Then
where = where & " AND [Contract End Date] between #" & _
Format(Me![txtStartDate],"mm/dd/yyyy") & "# AND #" & Format(Me![txtEndDate],"mm/dd/yyyy") & "#"
Else
where = where & " AND [Contract End Date] >= #" & Format(Me![txtStartDate],"mm/dd/yyyy") _
& " #"
End If