converting timestamp to normal dates

[shmuel]

hello,
I load to acsess tsv file from external database.

The dates in it are defined by timestamp like that
1364774802
1364774887
1364775537
1364776297

By proposing of external database experts I convert them to excell timestamp by formula:
=(A1/86400)+25569
And getting numbers like:
41365.00
41365.01
41365.01
41365.02
41365.02
If I use excell I just format cells to dates and I get normal dates:


1/4/13
1/4/13
1/4/13
1/4/13
How can I get normal dates from those timestamps in acsess (as excell is too limited)

Thanks!!!

[alansidman]

Import your data into Access. Assuming that there was only the one field imported, then in a query convert to the date format. Sheet1 is your table name and Field1 is the imported timestamp.

SELECT Sheet1.Field1, Format(Int(([Field1]/86400)+25569),"Short Date") AS Expr1
FROM Sheet1;

[shmuel]

Thank you a lot!!!
I created new column DATE using ZOOM based on timestamp as you suggested where Field1 is timestamp and action is table.
DATE: SELECT action.Field1, Format(Int(([Field1]/86400)+25569),"Short Date") AS Expr1
FROM action
I'm trying to run this but get the error message
"the syntax of the subquery in this expression is incorrect". Could you please suggest where is my syntax error? thanks again

[alansidman]

Why are you doing a subquery and not just an expression as I have demonstrated.

[shmuel]

thanks, I succeeded to create new variable Date using your suggestion. Another difficulty is converting the date into the Week Number.
I understand there is no such a function in assess, thanks again for your help!

[alansidman]

You can use the DatePart Function. Use the ww interval

http://www.techonthenet.com/access/f...e/datepart.php

http://datapigtechnologies.com/blog/...ber-in-access/

[shmuel]

great thanks again! I succeeded to do that
Another question I still have unfortunately. I'm trying to retrieve column in query that will be equal to day previous to day received from time stamp calculation...
I use formula Format(Int(([Field1]/86400)+25569),"Short Date")-1, but all the columns turn to be #error.
Thanks again for your kind help ))

[alansidman]

Need to change it to this: Format(Int(([Field1]/86400)+25569)-1,"Short Date")

Need the -1 in the Date calculation part of the expression and not in the format part.

[June7]

That is because Format function actually results in a string entity, not a true date value.