update query to convert time and minutes

[trythis]

I have a switch that produces a .txt file that I import into Access.

this file gives me military time like 1542 and 723 I would like to change this to 7:23 AM and 3:42 PM is this possible?

Also I get minutes in this format
00053
00114

I would like this to be
:53
1:14

Any help would be greatly appreciated.

Thanks,
Tina

 

[wazz]

format(1542,"##:##") gives you 15:42
cdate("15:42") gives you 3:42:00 PM