Access will not allow date criteria on a .xlsx linked table
Using Access 2016. Exporting data from Survey Monkey into an .xlsx file. In Access a created a linked table. I build my queries and try to use various date criteria's and Access will not return any results. My linked table for the date field is set as Date/Time with the format of yyyy-mm-dd h:nn:ss
Very simple query returns nothing. Remove date criteria and I see everything as would be expected. Yes, I have data that Date()-1 would pull. Using specific dates do not work either #11/05/2018#
SELECT SurveyMonkey.StartDate, SurveyMonkey.StoreNumber, SurveyMonkey.ServiceExperi
FROM SurveyMonkey
WHERE (((SurveyMonkey.StartDate)
Using 'Between DateAdd("d",-DatePart("d",
Very simple query returns nothing. Remove date criteria and I see everything as would be expected. Yes, I have data that Date()-1 would pull. Using specific dates do not work either #11/05/2018#
SELECT SurveyMonkey.StartDate, SurveyMonkey.StoreNumber, SurveyMonkey.ServiceExperi
FROM SurveyMonkey
WHERE (((SurveyMonkey.StartDate)
Using 'Between DateAdd("d",-DatePart("d",
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If the date field in the linked table shows as Short Text, it is NOT being seen as a date so you will need to convert it to a date in order to work with it as a date. if cDate(yourfield) does not work, then the format is not something that Access recognizes as a date. Please attach the spreadsheet so we can see what it actually is.
Just FYI - NEVER, EVER format date fields in a query EXCEPT to export them to Excel or some other system that needs a specific format OR when your Windows date format is not US standard. SQL Server REQUIRES string dates to be US standard format (mm/dd/yyyy) or the non-ambiguous year first format (yyyy/mm/dd).
As long as your date fields are left as date data types, they will sort and compare like dates. Once you Format() them, they become strings and will act like strings rather than dates.
Also, never put a format on a date field on a table. All that does is obfuscate the actual value. Format dates only (except as above) at the time of display.
All this is because dates are stored internally as double precision numbers with the integer portion representing the number of days since the origin date (12/30/1899 for Access but Excel and SQL Server and other RDBMS' use the same concept, just a different origin (0) date). The decimal is the amount of time since midnight so .5 is noon. .25 is 6 AM and .75 is 6 PM. To see how this works, open a code module and in the debug window, print out the current date and time.
print now()
11/6/2018 10:40:59 PM
print cdbl(#11/6/2018 10:40:59 PM#)
43410.9451273148
This shows that 11/6/2018 is 43,410 days after 12/30/1899 which is date 0.
All date arithmetic works on this numeric value so how you display a date doesn't affect how Access (or other apps) calculate differences, sort, and compare. Think of the nightmare of code logic if every date had to have some tag that explained its specific format.
Just FYI - NEVER, EVER format date fields in a query EXCEPT to export them to Excel or some other system that needs a specific format OR when your Windows date format is not US standard. SQL Server REQUIRES string dates to be US standard format (mm/dd/yyyy) or the non-ambiguous year first format (yyyy/mm/dd).
As long as your date fields are left as date data types, they will sort and compare like dates. Once you Format() them, they become strings and will act like strings rather than dates.
Also, never put a format on a date field on a table. All that does is obfuscate the actual value. Format dates only (except as above) at the time of display.
All this is because dates are stored internally as double precision numbers with the integer portion representing the number of days since the origin date (12/30/1899 for Access but Excel and SQL Server and other RDBMS' use the same concept, just a different origin (0) date). The decimal is the amount of time since midnight so .5 is noon. .25 is 6 AM and .75 is 6 PM. To see how this works, open a code module and in the debug window, print out the current date and time.
print now()
11/6/2018 10:40:59 PM
print cdbl(#11/6/2018 10:40:59 PM#)
43410.9451273148
This shows that 11/6/2018 is 43,410 days after 12/30/1899 which is date 0.
All date arithmetic works on this numeric value so how you display a date doesn't affect how Access (or other apps) calculate differences, sort, and compare. Think of the nightmare of code logic if every date had to have some tag that explained its specific format.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Norie is right. It looks like those dates have a timepart as well, which you need to take care of.
So, either cut the timepart as Norie showed, or - as you have done with success - filter on an interval (notice >= and <):
So, either cut the timepart as Norie showed, or - as you have done with success - filter on an interval (notice >= and <):
Where StartDate >= DateAdd("d", -1, Date()) And StartDate < Date()
second, make sure the data are in proper format before the querying.