troubleshooting Question

Querying SQL Server 2014 ‘date and ‘datetime’ fields from Access 2013

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft AccessMicrosoft SQL Server
2 Comments1 Solution181 ViewsLast Modified:
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:

Example Of Filter Screen
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.
ASKER CERTIFIED SOLUTION
Kelvin Sparks

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros