PC Review


Reply
Thread Tools Rate Thread

Code to check for proper date and time format

 
 
Linda RQ
Guest
Posts: n/a
 
      14th Sep 2007
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


 
Reply With Quote
 
 
 
 
tina
Guest
Posts: n/a
 
      14th Sep 2007
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


"Linda RQ" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>



 
Reply With Quote
 
 
 
 
Linda RQ
Guest
Posts: n/a
 
      14th Sep 2007
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


"tina" <(E-Mail Removed)> wrote in message
news:aenGi.540699$(E-Mail Removed)...
> 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
>
>
> "Linda RQ" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting number into proper date format Brandy Microsoft Excel Misc 3 7th Jul 2009 05:24 PM
Proper Code Format Danu Microsoft Access VBA Modules 2 19th Jun 2008 08:20 PM
Convert date + time text format to date format =?Utf-8?B?UGF1bCBIbw==?= Microsoft Excel Worksheet Functions 2 22nd May 2007 05:47 PM
How do I format text in proper noun format? =?Utf-8?B?SmVubiBNYXVyYQ==?= Microsoft Access Getting Started 1 24th May 2005 08:46 PM
code to convert date from TEXT format (03-02) to DATE format (200203) Gauthier Microsoft Excel Programming 0 22nd Sep 2004 03:26 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:14 AM.