Results 1 to 3 of 3

Using DateAdd, cannot show time portion when time result is 00:00:00

  1. #1
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,514

    Using DateAdd, cannot show time portion when time result is 00:00:00

    If I DateAdd a number of minutes which happens to land me on 00:00:00 portion of a date, I CANNOT seem to get my date variable to include the zeros. OK, it's really there, but I need to see/extract the portion later. Without it, testing the rest of the code is problematic since there's no guarantee the resulting date time calculation will be accurate.



    If I call Function testExpression(dteStart As String, lngLenStart As Long, lngLenEnd As Long, lngRate As Long) As String
    as testExpression(#1/8/2019 8:00:00 AM#,48,20,16)

    The calculation looks like
    dteEnd = DateAdd("n", ((48 - 20) * 16 * 60), dteStart) which just happens to land me on 01/27/2019 00:00:00, which is bizarre enough. To show the full date/time for this date/time, I have tried

    - variable as a Date type and as a string - no difference
    - format function
    dteEnd = Format(DateAdd("n", (lngLenStart - lngLenEnd) * lngRate * 60, dteStart), "mm/dd/yyyy hh:nn:ss") which shows 1/27/19 [no time portion]

    - FormatDateTime function as in (subbed numbers to shorten expression)
    dteEnd = FormatDateTime(DateAdd("n", ((48 - 20) * 16 * 60), dteStart), 0)
    dteEnd = FormatDateTime(DateAdd("n", ((48 - 20) * 16 * 60), dteStart), 1)
    dteEnd = FormatDateTime(DateAdd("n", ((48 - 20) * 16 * 60), dteStart), 2) all which show 1/27/19

    dteEnd = FormatDateTime(DateAdd("n", ((48 - 20) * 16 * 60), dteStart), 3)
    dteEnd = FormatDateTime(DateAdd("n", ((48 - 20) * 16 * 60), dteStart), 4) both showing 12:00:00 AM

    For my purposes, I suppose I'll be able to use the separated values as dteTime (e.g. 1/27/19) and dteHour but I might need to convert dteHour to a string to get leading zeros, but here's the question (finally):
    - why the #$LL does time portion not show when it's really there when using any of those formatting attempts? All I have to do is change a variable to move me off of that date/time and it displays as it should (e.g. change 60 to 61, result is 1/27/19 7:28:00 AM )

    After more than 3 hours, I'm stumped and will have to do a work around if there isn't a simple solution, such as split the date and time portions. I didn't post the whole function as it's a mess right now and I don't think it's the issue - save for possibly my use of any format functions.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,248
    I think it's an inbuilt Access "function" .

    If you look at a table directly with that datetime type of data - and set one time value to 00:00:00 then it truncates the display;

    LastOpen
    06/03/2018 13:20:00
    07/03/2018 12:31:00
    10/04/2018 15:26:00
    12/07/2017 09:04:00
    16/02/2018 11:09:00
    02/10/2017 09:16:00
    01/03/2018 09:37:00
    20/10/2017 16:21:00
    10/01/2017
    19/02/2018 12:21:00
    14/11/2016 13:20:00
    02/02/2018 08:56:00
    20/12/2018 10:20:00
    07/01/2019 12:48:00
    03/05/2018 14:58:00
    30/08/2018 14:25:00
    03/10/2018 17:05:00
    22/08/2018 10:08:00

    But if you query it's values with a format ;
    Code:
    SELECT tblDB_List.LastOpen, Format([lastopen],"dd/mm/yy hh:nn:ss") AS Expr1 FROM tblDB_List;
    LastOpen Expr1
    06/03/2018 13:20:00 06/03/18 13:20:00
    07/03/2018 12:31:00 07/03/18 12:31:00
    10/04/2018 15:26:00 10/04/18 15:26:00
    12/07/2017 09:04:00 12/07/17 09:04:00
    16/02/2018 11:09:00 16/02/18 11:09:00
    02/10/2017 09:16:00 02/10/17 09:16:00
    01/03/2018 09:37:00 01/03/18 09:37:00
    20/10/2017 16:21:00 20/10/17 16:21:00
    10/01/2017 10/01/17 00:00:00
    19/02/2018 12:21:00 19/02/18 12:21:00
    14/11/2016 13:20:00 14/11/16 13:20:00
    02/02/2018 08:56:00 02/02/18 08:56:00
    20/12/2018 10:20:00 20/12/18 10:20:00
    07/01/2019 12:48:00 07/01/19 12:48:00
    03/05/2018 14:58:00 03/05/18 14:58:00
    30/08/2018 14:25:00 30/08/18 14:25:00
    03/10/2018 17:05:00 03/10/18 17:05:00
    22/08/2018 10:08:00 22/08/18 10:08:00

    It displays correctly
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,514
    I guess it's not such a big deal that I couldn't work around it since the value is really there. It's just dumb that the format function on the vb side doesn't show what the sql side does. IIRC, it would print to the immediate window though.

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

Similar Threads

  1. Replies: 1
    Last Post: 08-09-2018, 03:11 PM
  2. Replies: 1
    Last Post: 05-21-2016, 07:51 AM
  3. Simple Line Chart field result over time
    By illicited in forum Reports
    Replies: 4
    Last Post: 01-23-2015, 04:42 PM
  4. Replies: 6
    Last Post: 11-10-2014, 02:14 AM
  5. Replies: 42
    Last Post: 03-01-2013, 05:58 AM

Posting Permissions

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