Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am fairly good with access/excel/etc. However I am running into a brick wall with a database I query regularly for information. It has date/time fields that are tracked in unix time format (number of seconds from 1/1/1970).

My end goal is for a user to set a start date and end date which will be passed to an access query as variables to return all rows between the start and end date.

Right now my workaround solution is to import all rows and create a separate column using Format and formatting the unix time date/time locally into a short date that I can filter easily on. However, as expected, this is terribly slow as the query has to import all the rows of the external database and then calculate the date/time field locally before display results.

I know there has to be a way to pass the query to the external database, but I just don't know what the syntax is going to be (and once that is figured out how to insert the temp variables into the syntax).

share|improve this question

1 Answer 1

you can simply pass the number to your query.
To convert an Access Date/Time value to unix epoch time, DateDiff("s", DateSerial(1970, 1, 1), YourDate) will give you the figure you need

SQLString = "SELECT * FROM YourTable WHERE Time < " & DateDiff("s", DateSerial(1970, 1, 1), YourDate)

This will hopefully give you an idea how to create the SQL statement you need

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.