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.