I need the date without the time

[Guest]

I have imported a table that contains dates with times associated. I've gone
into the table and changed the format to Short Date and changed the default
value to Date() but when I click on the record the time portion unhides and
the query I want to use to count entries by date is not able to count due to
the presence of the time value. Any suggestions?

 

[Roger Carlson]

Since dates are actually stored as double-precisions numbers, with the
integer portion being the date and the decimal portion being the time, you
can use the Int() function in an update query to remove the time portion:

UPDATE Table1 SET Table1.mydate = Int([mydate]);

Replace "Table1" above with your table name and "mydate" for your actual
date field.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jamie Collins]

Since dates are actually stored as double-precisions numbers, with the
integer portion being the date and the decimal portion being the time, you
can use the Int() function in an update query to remove the time portion:

UPDATE Table1 SET Table1.mydate = Int([mydate]);

The usual approach temporal data in a strongly typed language such as
SQL is to use temporal functions e.g.

UPDATE Table1 SET mydate = DATEVALUE(mydate)

Once the data has been scrubbed, a Validation Rule can be put in place
to ensure bad data does not reappear e.g. DATEVALUE([mydate]).

Jamie.

--