How to convert AS400 date to Access date/time

[sushmitha]

I have date coming from AS400 date.

Statedt is in yyyymmdd format
Statetm is in hhmmss000 (millis sec format)

For ex:
Statedt is 20080521
Statetm is 12300905

Here is the attached file I have with date and time
I need to find the most recent State by using date and time

Can anybody help me on this

 

[MSAccessRookie]

I am not an AS400 Expert, but I think this link might be of assistance and get you moving in the correct direction.


http://www.accessmonster.com/Uwe/Fo...400-date-1071101-to-date-11-01-2007-mm-dd-yyy

 

[sushmitha]

I am not an AS400 Expert, but I think this link might be of assistance and get you moving in the correct direction.


http://www.accessmonster.com/Uwe/Fo...400-date-1071101-to-date-11-01-2007-mm-dd-yyy

I have data coming from Excel sheet not AS400 directly. Also I have two fields both date and time
Just need to convert time to hh:mm:ss AM

 

[MSAccessRookie]

I have data coming from Excel sheet not AS400 directly. Also I have two fields both date and time
Just need to convert time to hh:mm:ss AM

I am not sure, but I thought the link show how to convert AS400 Format date and time to Access Format Date and time. The actual source of the data (AS400, Excel, etc.) should not matter. If not, then I hope that you are able to find some AS400 Experts to assist you.

 

[sushmitha]

I am not sure, but I thought the link show how to convert AS400 Format date and time to Access Format Date and time. The actual source of the data (AS400, Excel, etc.) should not matter. If not, then I hope that you are able to find some AS400 Experts to assist you.

1) Date format is Text. I tried to convert to Date/Time. But when I update table, it is deleting all records saying there is an error
2) Time stamp format is in 8 degit. For Ex: 12300905