Unix Timestamp to Access 2003

  • Thread starter Thread starter nms
  • Start date Start date
N

nms

I am working with a Remedy HelpDesk system that has an "arrivaltime" field
stored as a Unix timestamp that I need to convert to an Access date field to
be able to do a report by month (from 1 to 31 Dec for example). Does anyone
know an easy way to convert this "arrivaltime" field to an Access date field?
 
nms said:
I am working with a Remedy HelpDesk system that has an "arrivaltime" field
stored as a Unix timestamp that I need to convert to an Access date field to
be able to do a report by month (from 1 to 31 Dec for example). Does anyone
know an easy way to convert this "arrivaltime" field to an Access date field?
Click to expand...


Try something like:

DateAdd("s", arrivaltime, #1/1/1970#)
 
Thanks Marshall-I did that but I get the following error (I am trying to do
it in a query - is there somewhere else to do it?):

Data type mismatch in criteria expression.
 
nms said:
Thanks Marshall-I did that but I get the following error (I am trying to
do
it in a query - is there somewhere else to do it?):

Data type mismatch in criteria expression.

"Marshall Barton" wrote:
Click to expand...

Is this data imported into ms-access? I Would just perhaps add another Colum
arrivalTimeD


Then, run an update query such as:


update mytble set ArrivalTimeD = DateAdd("s", arrivaltime, #1/1/1970#) where
arrivaltime is not null

Then, for reports to group by month, week etc, you can use:


month([ArrivalTimdD])

or

day([ArivalTimeD]) in the query builder which then in turn will allow you to
use the sorting an grouping by month/week etc in the report writer....

I suppose, you could try casting the value

eg:
cdate(arrivaltime)

DateAdd("s", cdate(arrivaltime), #1/1/1970#), but really, why not get this
into a real date format..and then you all the year/month/day grouping
abilities without any hassle....
 
That function can be used used in a calculated field in your
query so your report can use a real date value whereever
needed.

I don't see how the function I posted could produce a
mismatch error. Since the message mentioned a criteria
expression, maybe you should post a Copy/Paste of the query
so I can check if you are doing what I thought you were
doing.
 
When you look at the timestamp what does it look like? Is it a number?
Type a couple of the UNIX timeStamp into your message.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
John-yes they are numbers:

Arrival_Time
1095702673
1143677967
1151679425
1156194651
1160088988
1161372526
1163096369
1163794590
1164676306
1168537127
1172258682
1172869447
1173817279
1174060491
 
Strange, I can convert all those with ease using Marshall's expression.

If I force an non-numeric character into the string, then I get the Type
Mismatch (13) error. SO I would check that the length of the field is the
same as the visible length. Look for leading or trailing characters that
are not in the number range, etc.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

nms said:
John-yes they are numbers:

Arrival_Time
1095702673
1143677967
1151679425
1156194651
1160088988
1161372526
1163096369
1163794590
1164676306
1168537127
1172258682
1172869447
1173817279
1174060491
Click to expand...
 
nms said:
John-yes they are numbers:

Arrival_Time
1095702673
1143677967
1151679425
1156194651
1160088988
1161372526
1163096369
1163794590
1164676306
1168537127
1172258682
1172869447
1173817279
1174060491
Click to expand...


I think you can check John's hypothesis by adding a
calculated field to the query:
Len(arrivaltime)
and then check if it has a 10 in every row.
 
Back
Top