Text field to Time

[Arlan]

I have a table created from a text report that I was able extract
certain data out of. It is a payroll report. What I need to do is
convert a text field into a time filed. The kicker is the way the
data is stored...for example...

TimeIn TimeOut
725a 455p
834a 1134a
1015a 335p
923a 225p
834p 623a
915p 334a
234a 924a

What I need to do is change the [timein] and [timeout] into actual
time. where the "A" designates "AM" and the "P" designates "PM". I
have searched through many posts and could not find a similar
solution. Once I have it converted into actual time, I need to the
get the "Time Worked". I also have a date filed like this:

PunchDate
08/12
08/11
12/11
11/11
10/29
08/02

How can I get the date field to reflect the current year...so 08/12
would read 08/12/2003

Thanks....

 

[Rick Brandt]

I have a table created from a text report that I was able extract
certain data out of. It is a payroll report. What I need to do is
convert a text field into a time filed. The kicker is the way the
data is stored...for example...

TimeIn TimeOut
725a 455p
834a 1134a
1015a 335p
923a 225p
834p 623a
915p 334a
234a 924a
What I need to do is change the [timein] and [timeout] into actual
time. where the "A" designates "AM" and the "P" designates "PM". I
have searched through many posts and could not find a similar
solution. Once I have it converted into actual time, I need to the
get the "Time Worked". I also have a date filed like this:

Try...
CDate(Format(Val([YourField]),"00:00") & IIf(Right([YourField],1)="a"," am"," pm"))

PunchDate
08/12
08/11
12/11
11/11
10/29
08/02

How can I get the date field to reflect the current year...so 08/12
would read 08/12/2003

CDate([YourField] & "/2003")
or
DateSerial(2003,Left([YourField],2), Right([YourField],2))

The first will use your regional settings to determine month vs. day when they are
both less than 13. The second is not affected by these settings.