Armen Stein wrote in
:

On 29 Dec 2009 16:52:00 GMT, "David W. Fenton"
wrote:

It is perhaps preferable to *you* -- for me, I vastly prefer a
date-only field. Where I need the time value, I use a separate
column for the time part.

But it's much harder to query date fields with time parts,
particularly if you are only ever displaying the date part.

I understand your preference. However, once one gets the hang of
querying dates with time components, it's easy. The time and data
components can be extracted from one field when necessary.
Date/Time math is easier when they're in one field.

99% of my querying against these fields is by date, and while it's
not that hard for *me* to query with:

WHERE DateField = #12/18/2009# AND #12/19/2009#

....but if you have user populations who write their own queries,
this is more than I want to ask them. On the other hand, these are
not fields that they are most likely to be querying on.

Aggregation on these date fields is also more complicated, as you
have to do it on an expression (DateValue(Created)) instead of on
the base field. Again, likely not a big deal, but it could be a
performance drain on a large table. It could also lead to issues if
you're using a server back end instead of Jet/ACE -- it could force
the aggregation client-side instead of server-side unless you move
the logic for doing the aggregation to a server-side component.

In general, my apps don't need the time component at all for the
created/updated fields. About the only ones that do are logging
tables, where time really is crucial. But even in that case, I'm
mostly searching on date ranges and ordering by time for each date.
That is, the time component is used only for ordering and not for
querying.

And having them in one
field takes half the space.

This is a difference that is not in any way relevant in our modern
operating environment. The only criticism in this direction I can
see that would be significant is if you index both fields, as
maintaining two indexes could add to overhead enough to be
noticeable, or you could bump up against the 32 indexes limit.

I was just away from the computer for a moment and realized that I
have an antipathy towards Now() that comes from its frequent mis-use
in a lot of the Access apps that I've encountered and been required
to update. A field populated with Now() and with a format of Short
Date is a red flag to me -- it's not that you won't sometimes want
to omit the time component for display, it's just that if that's the
only way it's displayed, then it's being populated with the wrong
function. Another example is Invoice Dates populated with Now() --
that's quite clearly an error, in my opinion, but I encounter that
kind of thing frequently (especially in apps created a long time
ago, in particular by an odd fellow named David Fenton who back in
1997 or so wasn't all that experienced with Access and created some
really bad messes).

I think a lot of the Access sample apps and the report wizards use
Now() and then format it for display. I can sort of see this, as it
makes them more forgiving for novice users. But it also trains them
to not choose the right function for the job.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/