Converting date and time fields

[JWCrosby]

Here's the situtation. I have to enter data from 400+
registration forms and each entry has a field for the date
for their exam (usually falls over a 7-day span) and the
time of their exam (must allow for am and pm). Right now
I have the fields formatted as date/time fields.

To speed up entry, I'd love to be able to simply enter,
say a "24" in the date field, and have it convert it to
04/24/04 (i.e., regular date/time format--I'd be able
to "hardwire" in the month and year) for use in reports.
Similarly, I'd like to be able to simply enter "830" in
the time field and have it become 8:30am.

Hope that's clear enough. What's the best way to do this?

 

[Allen Browne]

As you found, you cannot enter data like that into a field bound to a
Date/Time field. However, you may be able to use a pair of unbound fields
for the date and time, and build the date in their AfterUpdate events.

This example assumes the unbound text boxes are named txtDay and txtTime,
and the date/time goes into a field named ExamDateTime. (Be sure to set the
Format property of the unbound boxes to General Number so only numbers can
be entered.)

Private Sub txtDay_AfterUpdate
Dim lngLen As Long
Dim lngMinutes As Integer

lngLen = Len(Nz(Me.txtTime, vbNullString))
If lngLen >=3 And Me.txtDay <= 31 Then
lngMinutes = 60 * CLng(Left(Me.txtTime, lngLen - 2)) + _
CLng(Right(Me.txtTime,2))
Me.ExamDateTime = DateAdd("n", lngMinutes, _
DateSerial(Year(Date), Month(Date), Me.txtDay))
End If
End Sub
Private Sub txtTime_AfterUpdate
Call txtDay_AfterUpdate
End Sub