importing strange date format

[xzquala]

is it possible to add a date format to access so it can recognise the data I am trying to import?

the date is in this format:

yyyy:mm:dd 24:00:00.000

example:

2002:07:11 21:49:13.137

aka 11 july, 2002, @ 9:49 pm

the only part of it I actually need is the date. the data is in a tab seperated values text file that I create using an ad hoc.

when I try to import it into access, I get a date time format not recognized error.

any suggestions?

 

[Fizzio]

You could import the data as text, crop the data to only include the date then try a couple of things to convert it to a recognised date format.

eg
YourDate = DateValue(Left(ImportDate,4) & "/" & Mid(ImportDate,6,2) & "/" & Right(ImportDate,2))

You could use this in an update query and have the update to as the DateValueExpression.

The DateValue may work on the date bit alone eg
YourDate = DateValue(Left(ImportField,10) without the string creation bit above but I'm unable to test this at the minute.

 

[The_Doc_Man]

If you REALLY mean "Import" as in the File>>Import options from the standard menu bar, there is a way to deal with this, at least in part.

If the data source is fixed format, you can build an import specification in which you can select date order ymd and date separator ":" - but the trick will be to exclude the rest of this field (the time part) when you do the Import. The good thing is, you can save an import specification if you ever have to do it again.

If you really are doing an import, be sure to click the Advanced button so you can see the options to save specs and set up delimiters and date order and other good stuff like that.

 

[xzquala]

Thanks for you suggestions, I will try them out on duty this weekend.

btw- when I said import, I really meant it. it is coming from *.txt files. unfortunately, it is tab delimited, not fixed width.

but with these hints I should be able to get it right.

Thanx