Hello,
I typically import a text file of 3.5 million records using a pre-existing import spec file. However, I have several pending project for which I will have to query on date criteria. Currently data fields import as text.

If I try to change the datatype of the field, all the data is erased. If I try to change the import spec for this field from text to date, all the data is erased. I am very discouraged, stressed out, and devoid of ideas.

1) Will I be able to set criteria and return dates between two value if this field remains text? I doubt it.
2) Why can't I change the datatype from text to date in an existing field?
3) When I change the import spec (only change is the single field datatype from text to date) why isn't the data imported?
4) the text file looks like ...,"19520701","next field data", and yes, even though the text file is cvs the existing import spec is creates field deliminated format, and yes, I created a short file and deleted the " from the txt file with the same result.

Any insight, wisdom, suggestions, folk sayings, etc. would be most gratefully received.

There are a couple possible ways around this. You can either use a Scripting.FileSystemObject to import the file a line at a time, and use CDate() on your date fields, or you can create additional fields in the table once the data is imported, and then do an update query from the table to itself using CDate() or some other formula to move the date data to the date field. I used this syntax in an Update Query to move your OldDate text field to a NewDate date/time field in a table called "Dates":

UPDATE Dates SET Dates.NewDate = Mid([OldDate],5,2) & "/" & Right([OldDate],2) & "/" & Left([OldDate],4);

This returned this:

OldDate NewDate
19520701 7/1/1952

Does that help?


mmcdonal

Actually, you would have to use the same regular expressions if you were using the Scritping.FileSystemObject since CDate() doesn't work in this format.



mmcdonal