type conversion failure on time fields (1 Viewer)

[pbuethe]

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.

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.

 

[theDBguy]

Hi. Have you tried using the CDate() function against the formatted strings to see if you can convert them into date/time values first before appending them to the table?

You know, like CDate([StartTime1])? Just a thought...

 

[The_Doc_Man]

Use InStr to check for the presence of a colon BEFORE you try this conversion and if you don't have one, use the MID function to cobble together the parts with a colon added.

https://www.techonthenet.com/access/functions/string/instr.php

https://www.techonthenet.com/access/functions/string/mid.php

The following is "air code" for example only:

InputString = Trim$(SupposedTimeString) 
If Instr( 1, InputString, ":" ) = 0 Then
    lSLen = Len( InputString )
    GoodTime = Left( InputString, lSLen - 2 ) & ":" & Right( InputString, 2 )
Else
    GoodTime = InputString
End If

This presumes you will never get five non-blank characters in a string that has no colon. In essence, if you don't have a colon, take apart the string which should have 3 or 4 characters in it. Make a sandwich of a colon between the beginning and ending sub-strings.

 

[pbuethe]

I got back to this problem today after working on other priorities. I noticed that 2 time fields were mostly blank. Only 2 records had values in these fields. I removed these fields from the query and will try to update them separately.

After removing these fields and running the append query, there was only one field with type conversion failure. It turned out to be due to a time field containing a semicolon instead of a colon. After I corrected this, there were no type conversion failures. So that issue has been solved.

 

[theDBguy]

I got back to this problem today after working on other priorities. I noticed that 2 time fields were mostly blank. Only 2 records had values in these fields. I removed these fields from the query and will try to update them separately.

After removing these fields and running the append query, there was only one field with type conversion failure. It turned out to be due to a time field containing a semicolon instead of a colon. After I corrected this, there were no type conversion failures. So that issue has been solved.

Hi. Thanks for the update. Glad to hear you got it sorted out. Good luck with your project.

 

[Mark_]

Something to note:

Prior to importing I would STRONGLY suggest having a query that shows what you plan to add. I'd use a continuous form that can verify each filed and highlight those that it detects have issues. Then you'd have a user manually review the data prior to running your append query.

This would mean that for each record you would need to do a "Reasonability" check, just to make sure they are not putting dates in time fields and such. This can also catch when the person who filled in the PDF put 9am down instead of 9:00 and such.