I'm importing a CSV file in to my Access database. I have all of the Type Conversion issues worked out except 1. One of the fields I'm importing is the StartTime field.

The StartTime field is a custom format h:mm AM/PM. This column in the database table is set to Date/Time. Every line on the CSV file errors out with a Type Conversion Error.

I have an import specification for this file. I've tried adding a new column, formatting it as time (1:30 PM or *1:30:55 PM) and then copy / paste special values, and delete the original "time" field. I save the file and then re-open it. The StartTime field is now back to the custom format. Not sure what is going on here.

When I look at the data in the StartTime field, it looks like this 7:53:00 PM.

I've tried everything I can think of to import this field without any type conversion errors without any success.

Any suggestions, examples, tricks, tips, (or magic) to make this work would be GREATLY appreciated before I pull out the remaining hair on my head.....

Thanks in advance for your help......

CodePudding user response：

Don't import the file, but link it, linking that field as text.

Then use a simple select query to convert to true date/time values:

Select *, TimeValue([YourTextDate]) As TrueTime
From YourLinkedTable

Other fields may require conversion or modification as well.

Now, use this query for your further processing.