10 Replies Latest reply on Nov 17, 2016 4:10 PM by taylorsharpe

    MS Access ODBC import Date qualifier

    taylorsharpe

      I am converting a solution from MS Access using an Import of the MDB file via ODBC from Actual Technologies.  The database is on a Mac OS X 11.6 Server with FMS 15.0 v2.  The imports are done on the client side with FMPA 15.0 v2 on a Mac OS X client connecting to the server.  The imports are not done on the server side most because I didn't want to have to pay for the more expensive Actual driver.

       

      We run these syncs each nite pulling the info over.  I wanted to only pull over records from this year and tried to put a WHERE statement in the ODBC import based on the creation date.  However, whatever date format I use doesn't seem to work.  I've tried:

       

           >= '2016-01-01'

           >= 2016-01-01

           >= '01/01/2016'

           >= '2016-01-01 00:00:00'

       

      I've tried using a calculation that used the Date ( 1 ; 1 ; 2016 ) function, but that didn't work either.

       

      It may be that access stores the date data in a format that is a number like FM that is since a certain date and I need to convert it to a number somehow, but not sure if this is the case and what date Access would use. 

       

      Does anyone have a suggestion on what Value to put in to only find records for this year?  My workaround is just looking up the primary key field for the first record in 2016 and just searching greater than that key number, but it would save me a step to use the actual date. 

       

      Thanks for any suggestions.  And I'm mostly just glad to be getting these people away from Access!

       

      datequalifier.png