Question on date/time ( part philosophical, part techincal )

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am constructing a database that will handle schedules of techical resources
required at meetings. I realize that this is a job that is GREAT for Outlook
to handle, but for other reasons, we're looking to originate the scheduling
process with a database.

I have two fields (MtgStart and MtgEnd, both are Date/Time <short date>)
where I would like to make use of the start and end time portion of the
field, but have them use a common start date. Ideally, the user input form
(named: frmInput) is going to have a single "Meeting Date" field (named:
txtMtgDate) and then a "Meeting Start Time" field (named: txtMtgStart) and
"Meeting End Time" field (named: txtMtgEnd), since this will be least
confusing for the user.

What would be the best way to isolate these portions of the Date/Time fields
to accomplish this update? Philosophically, would it be better to instead
use a single Date/Time field for the date and then instead handle the time
element with two integer fields for the start and end times?
 
Even though you want to restrict the meetings to a single day, I'd still
store the complete date/time for the meeting start and meeting end. I think
you'll find the SQL is far easier when you've got the date and time in a
single field.

You can put unbound controls on your form for the meeting date and the two
time fields, and then put logic in your form's BeforeUpdate event to add the
meeting date to the two time fields for storage purposes. You can also put
code in the form's Current event to populate the three unbound controls from
the two dates, using the DateValue and TimeValue functions to strip off only
the portion you want.
 
It would not be unreasonable to have two controls on your form for entering
a data and time; however, it does take a bit more coding because even if you
are using two controls for entry and editing, you should still store the
entire date and time in one field in your table.

Here is a way to do that.
Create a third control that is not visible and is the bound control for the
date/time field. Then, in the After Update event of each, call a function
like this:

Private Sub StartDayAndTime()
With Me
.txtMtgBegins = Nz.txtMtgDate , 0) + Nz(.txtMtgStart, 0)
End With
End Sub

You will also need to split them out for current records in the Fomr Current
event:

If No Me.NewRecord Then
With Me
If Not IsNull(.txtMtgDate) Then
.txtMtgDate = DateValue(.txtMtgBegins)
.txtMtgStart = TimeValue(.txtMtgBegins)
End If
End With
End If
 
J. Keggerlord said:
I am constructing a database that will handle schedules of techical resources
required at meetings. I realize that this is a job that is GREAT for Outlook
to handle, but for other reasons, we're looking to originate the scheduling
process with a database.

I have two fields (MtgStart and MtgEnd, both are Date/Time <short date>)
where I would like to make use of the start and end time portion of the
field, but have them use a common start date. Ideally, the user input form
(named: frmInput) is going to have a single "Meeting Date" field (named:
txtMtgDate) and then a "Meeting Start Time" field (named: txtMtgStart) and
"Meeting End Time" field (named: txtMtgEnd), since this will be least
confusing for the user.

What would be the best way to isolate these portions of the Date/Time fields
to accomplish this update? Philosophically, would it be better to instead
use a single Date/Time field for the date and then instead handle the time
element with two integer fields for the start and end times?
Click to expand...


IMO, you should not separate the time from the date for
either start or end time. I don't know about your
situation, but I have been to more than a few meetings that
lasted several days. In any situation where a resource is
needed across a midnight, you will tie yourself and your
user interface in knots if the date part is not included
with the time part for both values.

It's fairly easy to allow users to enter an end time without
a date part and use code in the end text box's AfterUpdate
event to plug in the date part of from the start value:
If DateValue(txtEnd) = 0 Then
txtEnd = DateValue(txtStart) + txtEnd
End If
 
Ahhh, so *that's* how that is done! Thank you, Dave!!! I can't tell you how
much time you've saved me trying to figure this out!
 
Back
Top