General Date Input Mask


G

Guest

Hi
Does anyone know how to create an input mask for the General Date field type.
I would like users to enter the following format into my database but
realise the users will need some help with an input mask to get it right:
dd/mm/yyyy hh:mm:ss

many thanks
 
Ad

Advertisements

A

Allen Browne

It might depend on who your users are, but IMHO an input mask will not help.

Firstly, a quick data entry operator can enter some dates with just 3
keystrokes, e.g.:
4/9
and Access supplies the year. You certainly can't do that kind of thing if
you use an input mask.

An alternative solution is to give the user two unbound text boxes - one for
the date, and the other the time. You then programmatically combine the 2
values and assign them to the hidden text box that is bound to the actual
date/time field.

1. Paste these 2 functions into a standard module, where you can use it with
any form:
Public Function DateTimeCombine(txtDate As TextBox, _
txtTime As TextBox, txtCombined As TextBox) As Boolean
On Error GoTo Err_Handler
'Purpose: Assign the value of txtDate + txtTime to txtCombined.
Dim dt As Date
If Not (IsNull(txtDate) Or IsNull(txtTime)) Then
dt = DateValue(txtDate) + TimeValue(txtTime)
If Abs(DateDiff("s", dt, txtCombined)) = 0 Then
'do nothing
Else
txtCombined = DateValue(txtDate) + TimeValue(txtTime)
DateTimeCombine = True
End If
End If

Exit_Handler:
Exit Function

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler
End Function

Public Function DateTimeSplit(txtDate As TextBox, txtTime As TextBox, _
txtCombined As TextBox, Optional bUseOldValue As Boolean) As Boolean
On Error GoTo Err_Handler
'Purpose: Assign to value of txtCombined to txtDate and txtTime.
Dim varDateTime As Variant

If bUseOldValue And Not IsNull(txtCombined.OldValue) Then
varDateTime = txtCombined.OldValue
Else
varDateTime = txtCombined.Value
End If

If IsNull(varDateTime) Then
txtDate = Null
txtTime = Null
Else
txtDate = DateValue(varDateTime)
txtTime = TimeValue(varDateTime)
End If

Exit_Handler:
Exit Function

Err_Handler:
Call LogError(Err.Number, Err.Description, conMod & ".DateTimeSplit")
Resume Exit_Handler
End Function

2. In the Current event procedure of the form, add this line:
Call DateTimeSplit(Me.txtInteractDate, Me.txtInteractTime,
Me.InteractDateTime, False)

3. In the form's Undo event procedure:
Call DateTimeSplit(Me.txtInteractDate, Me.txtInteractTime,
Me.InteractDateTime, True)

4. Set up the AfterUpdate event procedure of the unbound date text box like
this:
Private Sub txtInteractDate_AfterUpdate()
On Error GoTo Err_Handler
'If user cleared the box, reset to the original date value.
If IsNull(Me.txtInteractDate) Then
If Not IsNull(Me.InteractDateTime.OldValue) Then
Me.txtInteractDate = DateValue(Me.InteractDateTime.OldValue)
End If
End If
Call DateTimeCombine(Me.txtInteractDate, Me.txtInteractTime,
Me.InteractDateTime)
Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler
End Sub

5. Set up the AfterUpdate event procedure of the unbound time text box:
Private Sub txtInteractTime_AfterUpdate()
On Error GoTo Err_Handler
'If the user cleared the box, reset to the original time value.
If IsNull(Me.txtInteractTime) Then
If Not IsNull(Me.InteractDateTime.OldValue) Then
Me.txtInteractTime = TimeValue(Me.InteractDateTime.OldValue)
End If
End If
Call DateTimeCombine(Me.txtInteractDate, Me.txtInteractTime,
Me.InteractDateTime)
Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top