Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
COURSE of the MONTH
15 days, 16 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Querying SQL Server 2014 ‘date and ‘datetime’ fields from Access 2013
Posted on 2016-07-19
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2 Comments
Active today
Access hasn't kept up with SQL Server. It does not recognise the Date datatype in SQL Server as a Date value (nor does it recognise DateTime2).
If you look at your linked tables, you'll see that it treats it as a text datatype (the default for everything it cannot resolve).
Try using
CDate([EventDate]) >= #1/10/2005# to make Access try and treat the value as a date - you may have to watch out for where the day, month, year is in the string).
Kelvin
If you look at your linked tables, you'll see that it treats it as a text datatype (the default for everything it cannot resolve).
Try using
CDate([EventDate]) >= #1/10/2005# to make Access try and treat the value as a date - you may have to watch out for where the day, month, year is in the string).
Kelvin
Active 2 days ago
Thanks for the heads up. After I posted the question I noticed that the date filters worked OK on the fields defined as date time. Just the 'date' fields were the issue.
Even though EventDate only contains a date and no time, I revised the definition in the base SQL table to datetime. The lookup is working as expected now.
I'll stay away from using the 'date' data type in any of the table defs.
Thanks again.
Even though EventDate only contains a date and no time, I revised the definition in the base SQL table to datetime. The lookup is working as expected now.
I'll stay away from using the 'date' data type in any of the table defs.
Thanks again.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month15 days, 16 hours left to enroll
741 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.