Code to check for proper date and time format

L

Linda RQ

Hi Everyone,

I am a complete beginner with code so it will probably take a few back and
forth questioning. I had help with this but the person who is helping me
doesn't know how to format the date in the part of the code below that we
put the little apostrophe in front of (Obviously I don't know the terms very
well either). My Date and Time field are together and looks like this

4/5/2007 2:34 pm

When we looked at format types in the help file, GeneralDate shows up with
the date and times in it but the user can just type in the date and check
the box.

I have a checkbox on my form and when it's checked, it makes the record
inactive. It's a yes/no field on my patients table. The user needs to
enter end date and time in all the records on 2 subforms before they check
this box for the data to be complete. So if they check the box and all the
records in the 2 subforms don't have the date in them they get a prompt.
This part is working. Now I just want to expand the requirement and make
sure they have both date and time in there. Currently they could only type
the date and get away with checking the box.

Thanks,
Linda


Private Sub PtActive_BeforeUpdate(Cancel As Integer)

Dim rstTherapy As Recordset
Dim rstPtLoc As Recordset
Dim dttThpyEndDtTm As Date
Dim dttPtLocEndDtTm As Date

Set rstTherapy = Me.sfmPtThpy.Form.Recordset
Set rstPtLoc = Me.sfmPtLocation.Form.Recordset

If Me.PtActive = False Then
If rstTherapy.RecordCount > 0 Then
rstTherapy.MoveFirst
With rstTherapy
Do Until rstTherapy.EOF
If IsNull(rstTherapy!ThpyEndDtTm) Then
MsgBox "Please enter BOTH an end date and time for all
therapies", vbExclamation
Cancel = True
Exit Sub
'Else
'dttThpyEndDtTm = rstTherapy!ThpyEndDtTm
'If Format(dttThpyEndDtTm) <> vbGeneralDate Then
'Cancel = True
'MsgBox "Please enter BOTH end date and time for all
therapies", vbExclamation
'Exit Sub
'End If
End If
rstTherapy.MoveNext
Loop
End With
End If

If rstPtLoc.RecordCount > 0 Then
rstPtLoc.MoveFirst
With rstPtLoc
Do Until rstPtLoc.EOF
If IsNull(rstPtLoc!PtLocEnDtTm) Then
MsgBox "Please enter BOTH an end date and time for all
locations", vbExclamation
Cancel = True
Exit Sub
'Else
'dttPtLocEndDtTm = rstPtLoc!PtLocEnDtTm
'If Format(dttPtLocEndDtTm) <> vbGeneralDate Then
'Cancel = True
'MsgBox "Please enter BOTH end date and time for all
therapies", vbExclamation
'Exit Sub
'End If
End If
rstPtLoc.MoveNext
Loop
End With

End If

End If





End Sub
 
T

tina

first of all, let's look at the table where the date/time value is stored.
is the data type of that field Date/Time? if not, it should be. once you're
sure you're storing the date and time as a date/time value, rather than just
text, it becomes easier to work with the value.

when you enter a date in a field with a Date/Time data type, it *always* has
a time - you just don't necessarily see it. if a specific time is not
entered, the time defaults to 12:00:00 AM (midnight, not noon). if a valid
time of 12:00 AM will *never* be entered, you could check for a valid time
with the following code, as

If Me!DateTimeFieldName = Int(Me!DateTimeFieldName) Then
Msgbox "Enter a valid time, please."
End If

but it might be easier to just set an input mask in the textbox control of
the data entry form - or in the table itself - as

99/99/00\ 00:00\ LL;0;_

the input mask will not accept a value that does not include a valid time,
though it *will* accept midnight (00:00:00 AM). so you don't need any
validation code to check for a time value. also, you'll probably want to set
the Format property to show the date and time the way you want, as

dd/mm/yy hh:nn AM/PM

hth
 
L

Linda RQ

Hi Tina,

Yep, it's a date and time field. I don't want to use a mask because the
users use the shortcut key strokes to enter the date and time and that
doesn't work with input masks. I tried that and nearly got fonged.

I'll try the code this weekend.

Thanks,
Linda
 

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

Top