I am encountering a similar situation with filtering a table using the Between condition on two dates.
This is a hotel/trip management application with Access frontend and backend. Reservations are made in advance. By selecting the reservation, a list of available rooms are to be shown for the time period of the reservation. This was done by querying a table of the BookedRooms.
The query was working fine, suddenly the available rooms wouldn't display for certain dates and ranges. (for example 9/12/2011 and 12/2/2011). At first we had a suspicion it was the Fiscal Year causing a problem. When the date range was across the fiscal year (Oct. 1st), the problem occured. However, the Select query worked with the dates in that range hardcoded into VB. Passing along the dates properly from the form to the query seems to be the problem. I attempted to format the date in the VB functions before passing them to the query.
Public Function GetStartDate() As Date
Public Function GetEndDate() As Date
I checked the result of the cdate (msgbox) and it still remained in the 9/12/2011 format.
Viewing the BookedRooms table on the backend had a strange quirk also. Opening the table in datasheet view is ok.
When clicking on the Date column to filter it, things got interesting. Selecting the Date filter Between for the range 9/13/2011 to 10/12/2011 gives an error message. Enter a valid date!
9/13/2011 to 10/8/2011 works fine and then changing the range works.
The bad ranges work after the initial range is successful. Clearing the filter and entering a bad range first will get the error message.
So I don't know what is going on with this Access app and database.
I hope somebody has an idea of where to proceed next.
My thanks in advance