Unix Timestamp to Access 2003

[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?

 

[Marshall Barton]

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?

Try something like:

DateAdd("s", arrivaltime, #1/1/1970#)

 

[nms]

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.

 

[Albert D. Kallal]

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:

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....

 

[Marshall Barton]

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.

 

[John Spencer]

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
..

 

[nms]

John-yes they are numbers:

Arrival_Time
1095702673
1143677967
1151679425
1156194651
1160088988
1161372526
1163096369
1163794590
1164676306
1168537127
1172258682
1172869447
1173817279
1174060491

 

[John Spencer]

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
..

John-yes they are numbers:

Arrival_Time
1095702673
1143677967
1151679425
1156194651
1160088988
1161372526
1163096369
1163794590
1164676306
1168537127
1172258682
1172869447
1173817279
1174060491

 

[Marshall Barton]

John-yes they are numbers:

Arrival_Time
1095702673
1143677967
1151679425
1156194651
1160088988
1161372526
1163096369
1163794590
1164676306
1168537127
1172258682
1172869447
1173817279
1174060491

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.

 

[nms]

Hi John-yhou were right about this. It is fixed now and it works. Thanks.