Short Date Format

[Peter Marshall at OCC]

When I format a Date/Time field for Short Date both in my table and form,
how can I prevent the time from being stored along with the date? For
example, if I enter a date of 02/14/04, the actual value being stored is
02/14/04 1:23:31 PM. This causes a problem when I try to search for records
whose date is 02/14/04 because none are just 02/14/04.

 

[Jeff Boyce]

Peter

Access uses a Date/Time data type to store, ... well, ... date AND time! If
you format a date/time field to only show you the date, you'll only SEE the
date (but the time is still there).

If you are using the Now() function, it records a point-in-time (i.e., date
and time). If you use the Date() function, it records the date, and a
time-value of 0:00:00 (still records time). If you've been storing the
Date(), you can search for a date = #2/14/04# and it will ignore the
0:00:00.

You may want/have to do an update query to replace all those 2/14/04 1:23:31
PM date/times with 2/14/04 0:00:00 AM date/times...

 

[Douglas J. Steele]

Just to add onto Jeff's suggestion, you can use the DateValue function to
return just the date portion of the Date/Time field, so that your Update
query would be something like:

UPDATE MyTable
SET DateTimeField = DateValue([DateTimeField])