Dear SJ:
The DateDiff function will do this. Details are available in the "help"
provided with Access.
By the way, a date "format" refers to how a date is displayed, not how it is
stored. I prefer the term "datatype" to describe how it is stored. This is
not just a personal pet peeve. My preference is because formatting, such as
is done with a format property or function, does something entirely
different to, and separate from what datatyping does. I have seen this
terminology mislead many people, and the confusion I've seen in this
newsgroup over that topic is myriad (lots of it).
For example, if there is a date/time datatype column in a table, then when
it is used in a criterion (WHERE clause) the "format" of this column has
absolutely no effect on how it functions in that WHERE clause. It may have
a lot to do with what you see when you look at a datasheet of the table, but
it has no effect on how that column functions in the criterion.
If the data stored in that column displays without its time component, that
does not mean the time component isn't there. If you filter:
WHERE MyDateTime = #1/1/2006#
this will find only those rows where the date/time is January 1, 2006 at
12:00:00 AM. It will not match any rows where the date/time is January 1,
2006 at 12:00:01 AM. You may not be able to "see" the time in the datasheet
because of the selected format, but that doesn't change the fact that it is
there.
Sorry to pick on you for such a minor issue. Perhaps it would never confuse
you. But I have regularly seen it confuse many.
Tom Ellison
