date/time

  • Thread starter Thread starter Bill H.
  • Start date Start date
B

Bill H.

Is it possible to have one date field and two controls on a form, whereby
one control shows the date (mm/dd/yy) and the other (for the same field)
displays the time?

When I set one control to date format and the other to time, things get
messed up.

:-(
 
Bill H. said:
Is it possible to have one date field and two controls on a form, whereby
one control shows the date (mm/dd/yy) and the other (for the same field)
displays the time?

When I set one control to date format and the other to time, things get
messed up.
Click to expand...

Messed up how? That should work just fine.
 
When I set one control to date format and the other to time, things get
messed up.
Click to expand...

For displaying the values, then it shouldn't be a problem.

If you let the user edit the things, though, then
(a) editing the date box will set the value to midnight on the date in
question,
(b) editing the time box will set the value to that time on Day Zero
(which is December 30, 1899 I think).

The final value entered will be whichever was the last one to be edited.

The workround would be to use the Form_BeforeUpdate event and set the
underlying field using something like

Dim tempValue as Date

tempValue = DateValue(CDate(txtDateBox.Value)) + _
TimeValue(CDate(txtTimeBox.Value))

me.recordset.Fields("DateTimeField").Value = tempValue


(this is exceedingly verbose, but you get the picture.)

Hope that helps


Tim F
 
Yeah, it needs to be edited, and that's just what happens!
Click to expand...
After a bit of testing, the workround is a little bit longer than I
anticipated. You might be better off just using a date picker control
(look up http://ccrp.mvps.org/download/ccrpdownloads.htm for example)

If you want to stick to two textboxes, then you'll need to use unbound
text boxes, and take over all the usual Access functions yourself. Try
this for a partially-tested example. TimeReceived is the name of the
database field that contains the datetime value; txtDateBox and
txtTimeBox are the textboxes, which have the format property set to
"dd/mm/yyyy" and "hh:nn:ss" respectively.


Option Compare Database
Option Explicit

Private Sub Form_Current()
' this gets called as soon as the form lands on a new
' record. If there's no value then empty the boxes,
' otherwise put the new values in
If IsNull(Me.TimeReceived) Then
txtDateBox.Value = Null
txtTimeBox.Value = Null

Else
' update the UNBOUND textboxes from the database value
txtDateBox.Value = DateValue(Me.TimeReceived)
txtTimeBox.Value = TimeValue(Me.TimeReceived)

End If
End Sub


Private Sub txtDateBox_BeforeUpdate(Cancel As Integer)
' do some validation checking before moving on
' if you want to allow nulls then put that here
'
' This has to be cascading If..ElseIfs because Or and And
' evaluate both their arguments and there will be Invalid
' Use of Null errors otherwise

' If IsNull(txtDateBox) Then
' Cancel = False
' ElseIf Not IsDate(... etc

If Not IsDate(txtDateBox) Then
Cancel = True

ElseIf Not TimeValue(txtDateBox) = 0 Then
Cancel = True

Else
' redundant: could put range checking in here
Cancel = False

End If

End Sub

Private Sub txtDateBox_AfterUpdate()
' echo the new value to the database
UpdateDateValue

End Sub

Private Sub txtTimeBox_BeforeUpdate(Cancel As Integer)
' see comments on txtDateBox_BeforeUpdate

If Not IsDate(txtTimeBox) Then
Cancel = True
ElseIf Not DateValue(txtTimeBox) = 0 Then
Cancel = True
Else
' redundant: could put range checking in here
Cancel = False
End If

End Sub

Private Sub txtTimeBox_AfterUpdate()
' echo the new value to the database
UpdateDateValue

End Sub

Private Sub UpdateDateValue()
' this creates a single datetime value out of the two boxes
' and relays that value back to the underlying recordset

If IsDate(txtDateBox.Value) And _
IsDate(txtTimeBox.Value) Then
Me!TimeReceived = _
DateValue(CDate(txtDateBox.Value)) + _
TimeValue(CDate(txtTimeBox.Value))

Else
Me.TimeReceived = Null

End If
Call Form_Current

End Sub
 
Back
Top