I am pretty new to MS Access and I'm experiencing difficulty SELECT'ing data from a table with a criteria expression involving a date. The data in my date&time column is formatted as dd.mm.yyyy hh:mm:ss
My test query is SELECT * FROM blog_entries WHERE created = '08.10.2012 00:00:00' and the error I am receiving is Data type mismatch.
What am I doing wrong?
The format of the column is irrelevant: a date/time is stored as a Double
number, a count of days and fractions of a day (e.g. 0.75=6pm) since midnight,
December 30, 1899.
Date literals in a query must be delimited by # rather than by '. I'm not sure
if the period delimiters within the date will work. To avoid possible
ambiguity between American mm/dd/yyyy and the more sensible international
dd/mm/yyyy, it's best to use the unamiguous ISO format (Access, programmed by
Americans, assumes mm/dd/yyyy regardless of system setting!). Try:
#2012-10-08 00:00:00#
Of course if your table field contains a time portion this will not match; if
that's an issue use
= #2012-10-08# AND < #2012-10-09#
--
John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also
http://www.utteraccess.com
