Results 1 to 6 of 6
  1. #1
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249

    24 hour time format not displaying correctly

    I've got a form, BOA ICR Times, that calls a set of date and time data from the Scenarios_tbl table. I have assigned a custom format to these dates and times as m/dd/yyyy hh:nn:ss . The table displays the time in 24 hour and the Senario combobox, ICRScenarioCombo, on the BOA ICR Times form shows them in my custom format as well (in column 2).



    The first issue is that when I call the date and times from ICRScenarioCombo, my Sim_Time_Data text box on the same form does not display the time in the correct format. It displays it in 12 hour format.

    The other issue I'm having is if the time is 00:00:00 (midnight), it does not show the time at all.

    I've attached my stripped down database if anyone would like to take a look at it. Sceanrio 1 shows the midnight issue and both sceanrios show the times not being formatted correctly.

    Thanks for any help you can give me.

    Keith


    Event Execution Database V1.13.zip

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    This looks like some kind of bug with the textbox to me, I could be wrong. But I've found a workaround.

    In your scenario combobox you can pull in the date/time fields twice, once just as the raw data, and the second time use the format() function for display purposes. When you use the format() function in the query it will actually be a string datatype. In the combobox's Column Width's property you can set the raw data columns to zero width to hide them from the user. Then, in the Sim_Time_Data and Sim_Time text boxes set control source to the formatted columns of the combobox, because this is a string data type and not a date/time it wont reformat the date. Finally, in text23 and text31 set the control sources to the appropriate unformatted date/time datatype columns of your control box to perform your calculations, and wrap that calculation in a format() function for display purposes.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the problem is you are referring to a combo list - which is actually text - widen the list to display all the columns and you will see the date is left justified.

    I don't see the issue of 12 hour format but change your controlsource to

    =CDate([ICRScenarioCombo].[column](3))

    and midnight will be displayed

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Ajax View Post
    the problem is you are referring to a combo list - which is actually text
    I also observed what the OP posted, the date is displayed in the wanted format in the combobox but then the date is reformatted in the textbox. Presumably the datatype within the combobox column object is a variant->date/time, no? ... My head hurts.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the problem with midnight is it is has no value.

    as I'm sure you know, datetime is stored as a double - the bit before the decimal point is the date, expressed as the number of days since 31/12/1899 and the bit after the time expressed as the number of seconds divided by 86400, the number of seconds in a day.

    so today is

    ?cdbl(date())
    44013

    and time
    ?cdbl(now())
    44013.3842708333

    midnight means no seconds so you would just get 44013 not 44013.0

    the combo is a string

    try this in one of the sim_time textboxes (remove formatting and input mask)

    =VarType([ICRScenarioCombo].[column](2))

    and it will return 8 - which is string

    https://docs.microsoft.com/en-us/off...rtype-function

    so the OP is trying to apply a date format to a string value - which means it attempts to convert the string to a date - and looks like loses something in the translation

  6. #6
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    249
    Quote Originally Posted by Ajax View Post
    the problem is you are referring to a combo list - which is actually text - widen the list to display all the columns and you will see the date is left justified.

    I don't see the issue of 12 hour format but change your controlsource to

    =CDate([ICRScenarioCombo].[column](3))

    and midnight will be displayed
    Thanks, that did solve my 12 hour display issue and it appears to now show midnight in my Sim Time Data and Sim Time text boxes.

    Quote Originally Posted by Ajax View Post
    the problem with midnight is it is has no value.

    as I'm sure you know, datetime is stored as a double - the bit before the decimal point is the date, expressed as the number of days since 31/12/1899 and the bit after the time expressed as the number of seconds divided by 86400, the number of seconds in a day.

    so today is

    ?cdbl(date())
    44013

    and time
    ?cdbl(now())
    44013.3842708333

    midnight means no seconds so you would just get 44013 not 44013.0

    the combo is a string

    try this in one of the sim_time textboxes (remove formatting and input mask)

    =VarType([ICRScenarioCombo].[column](2))

    and it will return 8 - which is string

    https://docs.microsoft.com/en-us/off...rtype-function

    so the OP is trying to apply a date format to a string value - which means it attempts to convert the string to a date - and looks like loses something in the translation
    Thanks for the insight on how the datetime is stored.

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

Similar Threads

  1. Replies: 7
    Last Post: 08-23-2019, 04:51 PM
  2. Join Not Displaying Correctly
    By spyldbrat in forum Access
    Replies: 22
    Last Post: 08-16-2018, 03:39 PM
  3. Hour Minute Format not time specific
    By jungmanish in forum Access
    Replies: 5
    Last Post: 05-06-2018, 03:46 AM
  4. Displaying Queries Correctly
    By cgjames in forum Reports
    Replies: 0
    Last Post: 01-18-2011, 06:54 PM
  5. [Access 2003] Displaying 24 Hour Time
    By Trainman in forum Access
    Replies: 4
    Last Post: 03-18-2009, 06:34 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