I am working on trying to automate our production numbers. Currently, our production numbers that are reported from the plants are based on shifts so I can't just use a date field to compile the data, I have to use the time stamp that is entered. Currently it is entered in an 8 digit format. In excel I am able to convert this to regular time (10:58 AM) by using the following formula =TIME(INT(J2/1000000),MOD(INT(J2/10000),100),MOD(J2/100,100)), where J2 is the 8 digit number. (I found the formula on stack overflow)
I have tried several things but haven't been able to convert in Access. Technically, it isn't necessary since I can just use an if then statement using the 8 digit numbers to assign shifts, but I would like to have the time stamps available so the plant managers can easily look at the data and see what happened when.
Thanks for any help.
Edit* Below is what the data pull for one item looks like in Excel.
*Update*
I ended up using the method Ajax suggested with a little change. In order to add the leading zeros I went ahead and reformatted the TrnTime field using TimeStamp: Format([TrnTime],"00000000"). From there I slightly adjusted what Ajax posted and used the following: Time: TimeValue((Left([TimeStamp],2)) & ":" & Mid([TimeStamp],3,2)).
Thanks for the help!