none
date/time field in table : how to put separate date and time on form RRS feed

  • Question

  • in my table is a date/time field , i like to keep it that way,

    but on my input  form i want the date separate from the time ?

    Can access do that ? or is there some programming involved ?

    Thursday, May 5, 2016 6:00 AM

Answers

  • You write "input form", so I assume that you want user to be able to enter and edit the date and time. That requires programming.

    Let's say the field is named MyDate.

    Place unbound text boxes txtDate and txtTime on the form. Set their Format property to a date format and a time format, respectively.

    Als place a text box bound to MyDate on the form, and set its Visible property to No, to hide it.

    Create the following event procedures (with a helper procedure) in the form's module:

    Private Sub Form_Current()
        If IsNull(Me.MyDate) Then
            Me.txtDate = Null
            Me.txtTime = Null
        Else
            Me.txtDate = DateValue(Me.MyDate)
            Me.txtTime = TimeValue(Me.MyDate)
        End If
    End Sub
    
    Private Sub txtDate_AfterUpdate()
        Call UpdateMyDate
    End Sub
    
    Private Sub txtTime_AfterUpdate()
        Call UpdateMyDate
    End Sub
    
    Private Sub UpdateMyDate()
        If IsNull(Me.txtDate) Then
            Me.MyDate = Null
        ElseIf IsNull(Me.txtTime) Then
            Me.MyDate = Me.txtDate
        Else
            Me.MyDate = Me.txtDate + Me.txtTime
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by tekoko10 Friday, May 6, 2016 1:38 PM
    Thursday, May 5, 2016 10:05 AM
  • Have you set the Format of txtDate to a date format such as Short Date, and that of txtTime to a time format such as Short Time?

    If you can't get it to work, could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then zip this copy and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Post a link to the uploaded and shared file in a reply here.

    Or register at www.eileenslounge.com (it's free) and start a thread in the Access forum. You can attach files up to 250 KB to a post there.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by tekoko10 Friday, May 6, 2016 1:38 PM
    Friday, May 6, 2016 12:47 PM
  • Indeed.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by tekoko10 Friday, May 6, 2016 5:42 PM
    Friday, May 6, 2016 4:54 PM

All replies

  • You write "input form", so I assume that you want user to be able to enter and edit the date and time. That requires programming.

    Let's say the field is named MyDate.

    Place unbound text boxes txtDate and txtTime on the form. Set their Format property to a date format and a time format, respectively.

    Als place a text box bound to MyDate on the form, and set its Visible property to No, to hide it.

    Create the following event procedures (with a helper procedure) in the form's module:

    Private Sub Form_Current()
        If IsNull(Me.MyDate) Then
            Me.txtDate = Null
            Me.txtTime = Null
        Else
            Me.txtDate = DateValue(Me.MyDate)
            Me.txtTime = TimeValue(Me.MyDate)
        End If
    End Sub
    
    Private Sub txtDate_AfterUpdate()
        Call UpdateMyDate
    End Sub
    
    Private Sub txtTime_AfterUpdate()
        Call UpdateMyDate
    End Sub
    
    Private Sub UpdateMyDate()
        If IsNull(Me.txtDate) Then
            Me.MyDate = Null
        ElseIf IsNull(Me.txtTime) Then
            Me.MyDate = Me.txtDate
        Else
            Me.MyDate = Me.txtDate + Me.txtTime
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by tekoko10 Friday, May 6, 2016 1:38 PM
    Thursday, May 5, 2016 10:05 AM
  • And if hou want myDate als een date /time field in the file ?,
    Thursday, May 5, 2016 9:25 PM
  • Hi tekoko10,

    "And if hou want myDate als een date /time field in the file ?,"

    please mention it clearly it quite confusing.

    Regards

    Deepak

    ,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 6, 2016 1:18 AM
    Moderator
  • And if hou want myDate als een date /time field in the file ?,
    In the setup that I described, MyDate is a field in the table that acts as Record Source of the input form. The date and time that the user enters in txtDate and txtTime will be combined and stored in the MyDate field.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, May 6, 2016 8:56 AM
  • An alternative, less robust approach if you're more interested in the display than in the actual data entry is to place two textboxes on your form, both bound to your date/time field.

    Format one as short date, and the other as short time (or whatever date/time formatting you want)

    Enable the one that is formatted as short date; lock/disable the one that is formatted as short time (you can also set the background and border to transparent to make it clear that the other is for data entry).

    The user enters the full date/time in the first box (eg: 5/6/16 05:24).

    The display splits the date/time into the separate boxes with your chosen formatting.


    Miriam Bizup Access MVP

    Friday, May 6, 2016 9:25 AM
  • yes , i did that , but i get an error 13 types don't match at    Me.MyDate = Me.txtDate + Me.txtTime

    so i tought i must do something else ...

    Friday, May 6, 2016 11:36 AM
  • Have you set the Format of txtDate to a date format such as Short Date, and that of txtTime to a time format such as Short Time?

    If you can't get it to work, could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then zip this copy and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Post a link to the uploaded and shared file in a reply here.

    Or register at www.eileenslounge.com (it's free) and start a thread in the Access forum. You can attach files up to 250 KB to a post there.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by tekoko10 Friday, May 6, 2016 1:38 PM
    Friday, May 6, 2016 12:47 PM
  • YEs , that 's it , i forgot the time field

    Many thanks Mr Hans,

    is this working in a continuous form ?

    Friday, May 6, 2016 2:44 PM
  • In a continuous form, the txtDate and txtTime text boxes will always display the same values in all visible records: the values for the active record.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, May 6, 2016 2:57 PM
  • with other words, it is not usable as input-output in a continuousform
    Friday, May 6, 2016 4:36 PM
  • Indeed.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by tekoko10 Friday, May 6, 2016 5:42 PM
    Friday, May 6, 2016 4:54 PM
  • What a. Pitty
    Friday, May 6, 2016 5:42 PM
  • You could create two extra text boxes in the Detail section, one with Control Source

    =DateValue([MyDate])

    and Format set to Short Date, and another with Control Source

    =TimeValue([MyDate])

    and Format set to Short Time. These text boxes will display the correct date and time in all records, but they are read-only, they cannot be used to edit the date/time.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, May 6, 2016 6:24 PM