none
trying to use a Json date in an access query (convert it) RRS feed

  • Question

  • here is what I have tried. My apologies as I don't use access often and as such, not fluent. It keeps asking for a value input for "rtime".

    Here is the SQL version

    SELECT Goo1.Jtime, Goo1.Lati, Goo1.longi, [rtime] AS Expr1
    FROM Goo1
    WHERE (((Goo1.Lati)>456760000 And (Goo1.Lati)<456826000) AND ((Goo1.longi)<-1186760000 And (Goo1.longi)>-1186892000) AND (([rtime])=(Goo1.jtime)/(86400)+#1/1/1970#));


    • Edited by Keshka Monday, September 24, 2018 6:54 PM
    Monday, September 24, 2018 5:11 PM

All replies

  • Try with:

    SELECT Goo1.Jtime, Goo1.Lati, Goo1.longi, Goo1.jtime/86400+#1/1/1970# As [rtime]
    FROM Goo1
    WHERE Goo1.Lati>456760000 And Goo1.Lati<456826000 AND Goo1.longi<-1186760000 And Goo1.longi>-1186892000;


    Gustav Brock

    Monday, September 24, 2018 5:39 PM
  • I got it!

    Where Jtime is the Json date value from Google location history, put this formula in your top field of design view

    rtime: CDate(CDbl(([Goo1].[jtime]/(86400000)+#01-Jan-70#)+(-7/24)))

    the -7 is to correct from GMT to your time zone.

    Monday, September 24, 2018 6:52 PM
  • ty Gustav, you were very close.

    Monday, September 24, 2018 6:55 PM
  • I was slightly closer. You don't need CDbl().

    Gustav Brock

    Monday, September 24, 2018 8:19 PM