date and time entry into text box

ozgirl
ozgirl used Ask the Experts™
on
Hi Experts
I have a text box that has an input mask of short time ie 13:00 and this value is recorded in the table ok.

however if i export the data to another table as date/time the date comes up with 18/12/30 13:00

is there a way i can keep the input box the same so the user only has to enter the time and it also records the date?

thanks for the help

ozgirl
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
By "it also records the date" I Assume you mean today's date? Then in the textbox's after update event:
Private Sub TheTestboxName_AfterUpdate ()
TheTestboxName = TheTestboxName + Date
End Sub

Why this works:
Access stores all dates as doubles as the number of days since 12/30/1899 12:00:00 AM  (Format(0,"mm/dd/yyyy hh:nn:ss AM/PM") = 12/30/1899 12:00:00 AM).  For example 09/11/2005 10:56:40 AM is stored as 38606.4560185185 or 38606.4560185185 days since 12/30/1899 12:00:00 AM  (CDbl(#09/11/2005 10:56:40 AM#) = 38606.4560185185).  You can input and display the date/time in any format you choose.  

Time without a date will default to 12/30/1899 since the integer portion of the double is zero.

Date without time defaults to midnight since the decimal portion of the double is zero.

Also keep in mind that Access is an American product so VBA is sometimes expecting mm/dd/yyyy even when the regional setting is set for something else.  yyyy/mm/dd will always work for VBA input.

You want the user to type "13:00", but the table to show today's date, with that time? This can be done in the "after update" event of the text box, see below.

The user can also type the date and the time, if you remove the input mask, that is.

(°v°)
Private Sub TextBox1_AfterUpdate()
    If Not Isnull(TextBox) Then
       If Int(TextBox1) = 0 Then TextBox = Date() + TextBox
    End If
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial