Table behavior (or design) problems

[setabery]

I have a table with seven fields, including an autonumber field as the
primary key, a date field, and a time field. The date field is
formatted as short date. The date field is the field that is giving
me problems.

My problem, I want to count the number of records that have the same
date. I designed a select query to count the number of records with
each date. The problem is that each record, even though they have the
same date, appears as a discreet record because the date field
includes not only the date that is entered but also inserts the time
that the record is entered. When you look at the data in table view,
the only data in the date field is the date that was entered.
However, when the mouse pointer is hovered in the date field and
clicked, the date that is entered along with the time of entry
displays.

How can I get this table to only store the date that is entered?

 

[Pat Hartman]

The format of a field has no impact on the data that is stored. In the case
of fields with a date/time data type, the actual data that is stored is a
double precision number with the integer portion representing the number of
days since Dec 30, 1899 and the decimal portion representing time of day in
hundredths of a second. Negative numbers are for dates earlier than Dec 31,
1899 and 0 is Dec 31, 1899. Storing a date in this fashion allows Access
(other relational databases use similar schemes) to use simple math to
calculate elapsed time. The stored value is converted to the display value
for presentation.

You are probably using Now() to populate the date field rather than Date().
It is better practice to store date and time in the same field so you can do
elapsed time calculations that span days.

To extract only the date portion of a field that contains date and time, use
the DateValue() function. To extract only the time, use the TimeValue()
function.

Select DateValue(yourdate) as OnlyDate From yourtable
Group by DateValue(yourdate);

 

[Guest]

Pat,
Great explanation other than "the decimal portion representing time of day
in hundredths of a second". The decimal portion in date/time field in Access
tables stores the portion of a day. For instance .5 represents noon and .75
is 6:00 PM.

One second is about 0.000011574
1/(24*60*60)

 

[Pat Hartman]

Thanks Duane.

Pat,
Great explanation other than "the decimal portion representing time of day
in hundredths of a second". The decimal portion in date/time field in
Access
tables stores the portion of a day. For instance .5 represents noon and
.75
is 6:00 PM.

One second is about 0.000011574
1/(24*60*60)