Results 1 to 15 of 15
  1. #1
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73

    Having current time stored in field

    Hello. I'm just trying to have the current time entered into a box, as easily as possible. Eliminating human error as much as possible. In a way that eliminates multiple ways of it being entered, too.
    So, I tried a text box tied to a field with its data type set as date/time. I set the format to general date which is supposed to have it display the date and then the time. But it only displays the date. It looks like that's an issue, a shortcoming, of the date picker. Is that accurate?


    But when I set it the format to short date, and then I use the date picker, it doesn't show the date. It shows the time, but only as 0:00 (instead of entering the current time).
    Am I missing something? It seems I'm close. But I can't get pas this point.
    Thanks in advance.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    set the textbox format to Medium Time

    Then the double click event for the textbox:

    Code:
    Private Sub txtTime_DblClick(Cancel As Integer)
        Me.txtTime = TimeValue(Now)
    End Sub
    User doesn't have to type anything, just double click.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    You can have Default Value property for date field set to Date(). The current date is entered when the new record is created.

    On form, you have the control linked to this field set invisible, or disabled - so user can't mess up with it.

    Probably you want to check that certain controls on form are filled whenever next record is selected - to avoid creating orphan records in table (i.e. records where only primary key and this date field are filled, so you have no way to link other tables to this one unless users are entering foreign key value manually).
    Last edited by ArviLaanemets; 07-27-2022 at 06:03 AM.

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Wouldn't

    On Got Focus me.txtTime = Format(Now(),"hh:nn") also work ??

    Just come on here to ask a question myself but seeing this, i thought i would comment but excuse me if my option doesn't work, I have an system update text box, just for logging my updating purposes i use on timer me.txtUpdate = Format(Now(),"hh:nn")

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Dave, any time you use Format() it converts the data to a string, so I would say No, that would not work, with the fact you lose the Time type.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    sounds like you are trying to select a date then add the current time so in the control afterupdate event

    myDate=myDate + time()

    and have the format set to general date

    or you can set the default value to now()

  7. #7
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    Wow!!! Very impressive. I only have a few minutes this AM to check into it. But the suggestion that I tried was yours, Davegri. And it looks like it worked perfectly. I'll keep this board posted. Thank you!!

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If time is all that is wanted, then Time() is a shorter way than TimeValue(Now) and is only using 1 function instead of 2.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    FYI this would be simpler:

    Me.txtTime = Time()

    Now() returns current date with time, Date() returns current date only, Time() returns current time only. No need for an extra function to peel time out of Now().
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    Thank you pbaldy. Yours work too. But I have the same issue with both solutions. And that is that after double-clicking I get what I want, the time entered into the text box. But the calculations I'm doing from them are off. So I looked into it, and when I click on the text box it goes from dispalying military time to a 12 hour format with am/pm added. Is there a way to have the double-click add military time, and not just have access displaying it? I tried setting the clock on the computer running access to military time, then resented the time, but that didn't make a difference. I tried what DMT Dave suggested. That didn't change anything.
    When I go to the table and I look at the values in these corresponding fields, I see military time. But, just like in the form, I see 12 hour am/pm time when I click inside the field. How do I make it strictly military time?

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    If you have no Format in the properties, then I get military time?

    hh:nn:ss format also appears to work ?

    Remember that the time is just a number relative to the day, so 0.5 would be 12:00 PM

    ? now() + 0.5
    28/07/2022 07:58:10
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  12. #12
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    As it turns out, what I was observing wasn't causing the problem I was having. I guess it was the type of calculation I was trying to run. I was just trying to get the total time spent by subtracting the earlier time from the later time, and adding them up. There's a lot of recorded times, so it's not just one start and end time. But when I did a DateDiff ("n", [field1]-[field2]) I got the right answer. But I never know which box will have the latest time. There's 9 boxes for times, I can't always subtract field1 from field9. I think I'll take what I've learned here and make it work with a query. Thanks again.

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Be careful if the times can go after midnight then you would need date and time.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I got the right answer. But I never know which box will have the latest time.
    use the abs function

    abs(DateDiff ("n", [field1]-[field2]) )

    but as WGM says if your time go over midnight, you need a date element as well

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    FYI this works across midnight with times alone:

    http://www.theaccessweb.com/datetime/date0004.htm

    That said, I'd have a date element if possible.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. generate current date and time after field entry
    By Smiffy3594 in forum Access
    Replies: 6
    Last Post: 02-20-2020, 08:49 AM
  2. Replies: 8
    Last Post: 07-18-2019, 01:36 AM
  3. Replies: 5
    Last Post: 07-06-2018, 01:30 PM
  4. Replies: 2
    Last Post: 01-16-2015, 04:22 PM
  5. Enter current date and time into subform field
    By tonybrecko in forum Forms
    Replies: 8
    Last Post: 06-16-2013, 09:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums