Guidance on entering and display date & time

[RichKorea]

I’m putting together a service database that uses a form based on a query for
the data input and display. I need to track when the call came in for
service, when the work started, and when the work was completed. My plan is
to use the three date/time values when preparing a report to calculate how
long it took to show up from when the call came in, and how long it took to
do the repair.

I’m thinking I want three fields in the table (Call, Arrive, and Finish).
There would be two combo boxes (one for the date and one for the time) for
each field, such that the user can enter the date and the time (or change the
date or time), and the values would then combine for saving in the field.
When displaying a record, the reverse would need to happen.

Is there a sample database or explanation on Microsoft.com that provides
guidance on how to handle dates and time?

One other question – the Active X calendar in Access works great for
entering the date. Is there something similar for entering the time?

Thanks,
Rich

 

[Klatuu]

It would be easier if you just use one text box to enter the date and time.
If you use an Input mask like this:
99/99/0000\ 99:99\ >LL;0;_
It will help guide the user through the entry and it will not allow invalid
entry formats. Note, the above only accepts hours and minutes and either am
or pm. If you need to add seconds, change 99:99 to 99:99:99

Then in the format property for the control select General Date

To split the data and time will only make things more difficult.

 

[John W. Vinson]

I’m putting together a service database that uses a form based on a query for
the data input and display. I need to track when the call came in for
service, when the work started, and when the work was completed. My plan is
to use the three date/time values when preparing a report to calculate how
long it took to show up from when the call came in, and how long it took to
do the repair.

I’m thinking I want three fields in the table (Call, Arrive, and Finish).
There would be two combo boxes (one for the date and one for the time) for
each field, such that the user can enter the date and the time (or change the
date or time), and the values would then combine for saving in the field.
When displaying a record, the reverse would need to happen.

Is there a sample database or explanation on Microsoft.com that provides
guidance on how to handle dates and time?

One other question – the Active X calendar in Access works great for
entering the date. Is there something similar for entering the time?

Thanks,
Rich

Dave's absolutely right: do NOT use separate textboxes for the date and time,
it just makes things harder, not simpler.

I wouldn't require the user to type either. Depending on how you are entering
the record, you could either set the Default Value of the field to =Now() to
fill in the date and time the instant a record is started (this would probably
be appropriate for the Call field); or use a single line of easy VBA code in
the textbox's DoubleClick event:

Private Sub txtArrive_DoubleClick()
Me!txtArrive = Now
End Sub

where txtArrive is the name of the textbox bound to the Arrive field. The user
can still overtype it if needed (and if that's permissible...!).

You can easily calculate the time on site etc. using the DateDiff() function
in your report, or in the query upon which you base the report.

 

[RichKorea]

Dave's absolutely right: do NOT use separate textboxes for the date and time,
it just makes things harder, not simpler.

I wouldn't require the user to type either. Depending on how you are entering
the record, you could either set the Default Value of the field to =Now() to
fill in the date and time the instant a record is started (this would probably
be appropriate for the Call field); or use a single line of easy VBA code in
the textbox's DoubleClick event:

Private Sub txtArrive_DoubleClick()
Me!txtArrive = Now
End Sub

where txtArrive is the name of the textbox bound to the Arrive field. The user
can still overtype it if needed (and if that's permissible...!).

You can easily calculate the time on site etc. using the DateDiff() function
in your report, or in the query upon which you base the report.

Hi John,

Fortunately or unfortunately, I didn't see your follow-up post to Dave
"Klatuu" Hargis' post, so I plowed ahead and created a test database to see
if I could do what I wanted (Dave did say not to try, he just said it would
be difficult). I'm a little leery of Now(), as our service engineers would
be too tempted to just accept the Now() value, rather then entering the
correct date and time. I also like the idea of letting the service engineers
keep their hand on the mouse as much as possible (my form has eight combo
boxes and eight text fields, and then seven date/time fields - management's
crazy about collecting time statistics).

I set up my test database with three date/time fields (general date format),
and then I set up my form with three text boxes (short date format) that
connect to the table, and then I added three more text boxes (medium time
format), using the formula =[Call_Time]-Int([Call_Time]) to figure out the
hours and minutes values. To enter date values, I use the Calendar Control
11.0 that pops up on a Mouse Down event, and to enter hours and minutes, I
added a scroll bar control that's set to 15 minute steps (close enough for
what we need). The scroll bar Update event causes the new time to get added
to the associated date text box, which in turn updates the hours/minutes
display. To wrap it all up, I added a couple of text boxes to show the time
delta results using DateDiff.

If you'd be interested in taking a look at my test database, let me know how
to post it or where to send it.

Thanks,
Rich