Code to check for proper date and time format

Discussion in 'Microsoft Access Form Coding' started by Linda RQ, Sep 14, 2007.

  1. Linda RQ

    Linda RQ Guest

    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
     
    Linda RQ, Sep 14, 2007
    #1
    1. Advertisements

  2. Linda RQ

    tina Guest

    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" <> wrote in message
    news:...
    > 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
    >
    >
     
    tina, Sep 14, 2007
    #2
    1. Advertisements

  3. Linda RQ

    Linda RQ Guest

    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" <> wrote in message
    news:aenGi.540699$...
    > 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" <> wrote in message
    > news:...
    >> 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
    >>
    >>

    >
    >
     
    Linda RQ, Sep 14, 2007
    #3
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jim Jones

    Q. How do I code a button on subform to open pop up to proper record?

    Jim Jones, Mar 3, 2004, in forum: Microsoft Access Form Coding
    Replies:
    4
    Views:
    145
    Jim Jones
    Mar 4, 2004
  2. Guest

    Open form code to enter a date in short date format

    Guest, Jun 30, 2005, in forum: Microsoft Access Form Coding
    Replies:
    5
    Views:
    294
    Marshall Barton
    Jul 5, 2005
  3. Martin Dashper

    Table Date/Time format in code

    Martin Dashper, Feb 22, 2006, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    180
    Martin Dashper
    Feb 22, 2006
  4. Guest
    Replies:
    1
    Views:
    185
    Rick Brandt
    Sep 13, 2006
  5. savigliano

    converting general date format data into short date format

    savigliano, Nov 27, 2006, in forum: Microsoft Access Form Coding
    Replies:
    3
    Views:
    845
    John Vinson
    Nov 27, 2006
Loading...

Share This Page