pbuethe
Returning User
- Local time
- Yesterday, 23:25
- Joined
- Apr 9, 2002
- Messages
- 210
I have the following scenario: data is entered into a fillable pdf. This is then read into an Access table where all fields are Text. Some of the fields represent time values. I need to append the data from this table to another table where the time values are Date/Time type. When I run the append query, I get type conversion failures. The problem seems to be that some of the times are entered without a colon, e.g. 935 instead of 9:35, 1322 instead of 13:22, etc. To get around this I tried putting an expression on my time fields in the append query, e.g.
This seems to format the time correctly for values with and without the colon, but throws the type conversion error on ALL the records, not just the ones without a colon. How can I get this to work without the type conversion failure? Thanks for your assistance.
Code:
StartTime1: IIf([StartTime12]>"",TimeValue(IIf(Left(Right([StartTime12],3),1)=":",[StartTime12],Left([StartTime12],Len([StartTime12])-2) & ":" & Right([StartTime12],2))),"")
This seems to format the time correctly for values with and without the colon, but throws the type conversion error on ALL the records, not just the ones without a colon. How can I get this to work without the type conversion failure? Thanks for your assistance.