Storing Time with Date

[Guest]

I am working with a database that uses dates and times frequently. Users are
entering times into fields (InTime, OutTime, etc.). I have read some posts
that say it is always best to store a date with a time. I was not aware of
this. The date and time are separate fields in the form/table; the form is
for a single day's events, and code on BeforeInsert populates each record
with that date when a new record is created, etc.

Should I be storing the date in each time field? If so, what is the best
way to do this? So the user enters the time only, in 24hr format, but the
table stores the date in the same field. I want the time fields to be blank
before the user enters anything. Is there something I could do with the
format property, input mask, or default value, or perhaps code something on
AfterUpdate (i.e. Me.InTime=[Date]+[Time])

Does anyone have any suggestions on the best way to handle this?

Thanks in advance.

DEI

 

[Guest]

Well, it depends. Usually, it is best to store the date and time together in
one field; but, without knowing exactly what you are doing, it is difficult
to make a specific recommendation.

If you have a parent record for the day with the date and the child records
represent the times associated with that date, it might be reasonable to
carry just a time, but not likely. If, on the other hand, we are talking
about a single table record source and you are contemplating have a date
field and time fields, then definitely not. Your idea about using the After
Update event is correct, except in the way you would do it. For example
Assume [Time] = 16:51 and [Date] = 10/10/2006

If you code it as you did in your post
Me.InTime=[Date]+[Time]
The result would be 10/10/200616:51

If you code it like this:
Me.InTime= #[Date]# + #[Time]#
The result would be:
10/10/2006 4:51:00 PM

Now, one other caution. Do Not name fields, contorls, or anything else
using reserved words like Date, Time, Name, Day, Year, Month, etc. You will
be asking for problems.

 

[Guest]

Thank you. I am in fact working with a single table record source, so I will
store the time and date in the same field, not in separate fields.

Thank you for your assistance.

Well, it depends. Usually, it is best to store the date and time together in
one field; but, without knowing exactly what you are doing, it is difficult
to make a specific recommendation.

If you have a parent record for the day with the date and the child records
represent the times associated with that date, it might be reasonable to
carry just a time, but not likely. If, on the other hand, we are talking
about a single table record source and you are contemplating have a date
field and time fields, then definitely not. Your idea about using the After
Update event is correct, except in the way you would do it. For example
Assume [Time] = 16:51 and [Date] = 10/10/2006

If you code it as you did in your post
Me.InTime=[Date]+[Time]
The result would be 10/10/200616:51

If you code it like this:
Me.InTime= #[Date]# + #[Time]#
The result would be:
10/10/2006 4:51:00 PM

Now, one other caution. Do Not name fields, contorls, or anything else
using reserved words like Date, Time, Name, Day, Year, Month, etc. You will
be asking for problems.

I am working with a database that uses dates and times frequently. Users are
entering times into fields (InTime, OutTime, etc.). I have read some posts
that say it is always best to store a date with a time. I was not aware of
this. The date and time are separate fields in the form/table; the form is
for a single day's events, and code on BeforeInsert populates each record
with that date when a new record is created, etc.

Should I be storing the date in each time field? If so, what is the best
way to do this? So the user enters the time only, in 24hr format, but the
table stores the date in the same field. I want the time fields to be blank
before the user enters anything. Is there something I could do with the
format property, input mask, or default value, or perhaps code something on
AfterUpdate (i.e. Me.InTime=[Date]+[Time])

Does anyone have any suggestions on the best way to handle this?

Thanks in advance.

DEI