Error Importing Date Field in Text File

[Guest]

Hi all,

I have a problem that is driving me insane. I'm trying to import a comma
separated file (with " as text delimiter). Two of the fields are written
like this: "DD/MON/YY" eg. "17/Dec/02"

I'd like to import this value into a field of type Date. But when I try to
import the text file, it generates errors. My confusion is, not all records
with the date field generate errors. And I can't figure out why. There's no
pattern that I can identify. It's simply, "sometimes it works, sometimes it
doesn't."

If I make the field Text instead of Date, the data imports without error.
But I'd like to have the field Date if at all possible, to prevent me from
having to do conversions later when I perform comparisons.

If anyone has any thoughts, I'd appreciate it. FYI: I'm using Access 97.

Thanks,
Jay

 

[John Nurick]

Hi Jay,

Is it possible that the problematic data was entered by an old-school
typist who didn't care about the difference between 1 and l or 0 and O?

Perhaps the best thing to do is to import it as text and run queries to
identify and clean up the problematic values. For instance, if the
problem is what I speculated above, in Access 97 you'd have to write a
custom VBA function to replace the lower case l with numeral 1, and call
this in an update query. (Later versions of Access have a built-in
Replace() function.)

Finally, add a date column to the table and use an update query to
convert the text values to dates. The CDate() function happily accepts
strings like "17/Dec/02".

 

[Guest]

Try setting up an import specification. Do the import once by hand. When
you get to the Import Wizard dialog, Click on the Advanced button. Here you
will be able to assign data types and field names. Then save your options as
an import spec with a name. Then you enter the name you save it as in the
Specification field in your macro.