I have years of experience developing Access application with Access backend DB’s. I am now working on an Access 2013 application with a SQL Server backend.
I am implementing a ‘filter’ form that I have created dozens of times in prior Access apps. There are unbound fields at the top of a continuous form. Whatever the user enters in the unbound fields is used to filter the records shown on the bound part of the continuous form. They look like this:
The filter form is working correctly on numeric and text fields but I am having issues filtering dates.
Using degug.print I verified that the SQL generated from the form is correct. This is the generated SQL:
Select * From dbo_vtblEvents Where [EventDate] >= #1/10/2005# And [EventDate] <= #1/30/2005#
I also verified that there is data in the view within the dates specified.
I get zero records on the screen when the query is used as the recordsource of the form.
In the base table ‘EventDate’ is defined as data type ‘date’
I have the view ‘dbo_vtblEvents’ linked to my Access app. When I bring up the view in the table designer, field ‘EventDate’ shows as data type short text.
Event date also shows on the screen as ‘2005-10-10’ instead of “1/10/2005”.
I’m getting the feeling that querying/filtering and even displaying SQL dates is not as straightforward as filtering Access dates.
Does anyone have insight into querying SQL Server dates form an Access application? How do I get the dates to show on the screen in standard ‘1/10/15’ format rather than ‘2005-01-10’?
Any help would be appreciated.