Consulting

Results 1 to 13 of 13

Thread: Unable to assign number to date type field

  1. #1
    VBAX Regular
    Joined
    Jun 2022
    Posts
    7
    Location

    Unable to assign number to date type field

    Hi all,

    I have a json file that I import into excel.

    an example of one of the dates in the json file is 200834905 (this is the value for the user in the example below).

    I have been pulling this date and data from other sheets into another sheet using ADO an example of a statement is

    SELECT [Result], [Date] FROM [Results$A1:C1012] WHERE [PlayerName] = ' orchidIove? '

    the date returned for this user via ADO is "22/05/2022 5:25:21 PM" and 44703.7259375 as a double

    So I have no problems with the above but I want to import this information via the json file into an access database.

    Using a very similar method (VBA ADO connection in an access database) I attempt to assign a value such as 200834905 from the json file to an access database such as RS("FleetJoinDate").Value = Value(7)

    This breaks the code with error "out of present range" which makes sense as the json value is too large to fit in the date data type that the FleetJoinDate field is set to.

    I cant CDate the json value and have run out of ideas as to where to go next.

    Hopefully this is enough information to convey what is going on.

    Any help would be appreciated. Thanks

  2. #2
    VBAX Regular
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    86
    Location
    So how is that number generated meant to mean to be able to be equal to what you posted?
    How does Excel know how to convert it?

    I would have thought you would need a UDF to convert to a decent date format for Access?
    There does not appear to any defined format for a date in JSON?

    https://stackoverflow.com/questions/...on-date-format

  3. #3
    VBAX Regular
    Joined
    Jun 2022
    Posts
    7
    Location
    I haven't had time to resolve this yet but I have since found out that it is coming from a unix date format. Now that I know that I should be able to apply a formula to it to represent a access date

    I'll post the answer when I get to it

  4. #4
    VBAX Regular
    Joined
    Jun 2022
    Posts
    7
    Location
    Still having issues with this. Using 200834905 as an example which should equal "22/05/2022 5:25:21 PM"

    Given its a unix date which is seconds after 1970 the following should work;

    RS("FleetJoinDate").Value = DateAdd("s", Value(7), "1/1/1970 00:00:00")

    It comes out as "13/05/1976 11:28:25 AM" though.

  5. #5
    VBAX Regular
    Joined
    Jun 2022
    Posts
    7
    Location
    I'm just going to change the field to long until I can figure it out.

  6. #6
    VBAX Regular
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    86
    Location
    Quote Originally Posted by Zerkin View Post
    I'm just going to change the field to long until I can figure it out.
    This site does not give anywhere near that?
    https://www.epochconverter.com/

  7. #7
    VBAX Regular
    Joined
    Jun 2022
    Posts
    7
    Location
    No gasman. Tried to paste but the site wont let me. You can enter the data and see the result. it's not valid

  8. #8
    VBAX Regular
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    86
    Location
    Well I would not say not valid, just not what you think it is?
    If you look at that site, the number for today is far greater?, so you need to find out what that number is meant to represent. It does not appear to be seconds?, as if you use that in DateAdd() and 01/01/1970, it comes nowhere near your date?
    So how does excel get it correct?

  9. #9

  10. #10
    VBAX Regular
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    86
    Location
    That appears to give the same as DateAdd and "s"

    ? exceldatetime(200834905 ,False)
    13/05/1976 11:28:25

    ? dateadd("s",200834905,#01/01/1970#)
    13/05/1976 11:28:25

  11. #11
    VBAX Regular
    Joined
    Jun 2022
    Posts
    7
    Location
    After looking into this further I was mistaken in thinking that the date was interpreted by one ADO connection and not another. I can't get anything to convert the supplied number as a valid date.

    So I'm going to try and track down the people who are exporting the data and see what's going on.

    I'll post here when I make any progress. Thanks for the help so far.

  12. #12
    VBAX Regular
    Joined
    Jun 2022
    Posts
    7
    Location
    Turns out the date stamps are based on a custom start date of "2016-01-06 00:00:00".

  13. #13
    VBAX Regular
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    86
    Location
    Quote Originally Posted by Zerkin View Post
    Turns out the date stamps are based on a custom start date of "2016-01-06 00:00:00".
    Makes a little more sense :-)

    However if seconds, still not your value?
    ? dateadd("s",200834905,#01/06/2016#)
    18/05/2022 11:28:25

    You said it was meant to be "22/05/2022 5:25:21 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
  •