Access Query Help

I need to do some analysis on data from our trouble ticket system. I can get the data out in a .CSV file (no ODBC connections are allowed). When I import into an Access table, it will only import as Text. I need to convert some of the fields to Date/Time in order to measure how long tickets stay in each stage.

I've tried building the table first with the proper fields set as Date/Time, but I receive import errors for every field marked as such. I've tried removing the quotes around the entries in the .CSV file, but that didn't work either.

I need an update query that will convert the field from Text to Date/Time. I've looked through an Access book, searched the web, etc... but the proper syntax is eluding me.

In case you couldn't tell, I'm such an Access newb. I'm a Sys Admin, we don't do apps...

Gimme an example of a date you are tryign to pass in? (ie. the actual text in the csv.)

Open the csv in excel first as a csv, then cut and paste into the access table.

This is what the CSV looks like in notepad

"4/24/2006 10:09:53 AM"

The problem with cutting and pasting is our region does 1200+ trouble tickets a month. I really need some form of automation in order to get the info back out before 2007.

That is a valid date time for an access date/time field. It's possible that one of the entries in your CSV is bad however. The best way to find out is to try and import and let it dump the errors into an error table and then go through them to see what happend.

I still reccomend opening it up into an excel file to help get rid of the "'s and then you can import the excel file into access if you want.

Have you tried opening it with Excel?
I know when you tell it what your delimiter is , it previews how the data will be broken out, maybe giving you an easy way to see what's breaking it.

Or, and I could be making this up, maybe you don't need Access and Excel will do just enough to give you the info you need.

Woops: I am a dirty skimmer. Still, is there a special reason Excel won't cut it? What does an entire line of the CSV look like, not just the date?

Do you have a grep-like tool you can use to try to find a line that's missing a comma, or more likely a line with an Irish name in it(O'Reilly, O'Brian O'Dammitthissinglequotebrokemyimport)?

If you're going to have to do this monthly, I'd knock out a perl script to handle the import and any errors/data scrubbing, but I'm weird like that.