I’ve got this text file that I import into Access. One of the fields is supposed to contain a date, but a bunch of records don’t. Instead they contain the word “none”. When I import, if I set the data type to ‘date/time’ it wants to delete those records without dates in them. If I set it to text, then the information is no longer stored as a date, and I need it to be so I can compare it to other fields that DO have dates in them. Is there a way around this problem? I need it to see the date as a date, but I don’t want to delete the word “none” .
And there are other records with nothing in that field, so blanking out the word “none” makes those records look just like the ones with no value in that field, and I want to be able to tell the difference between them.
Anyone know if there’s a quick and easy way to do this? I have to import the text file daily, so I need a quick simple solution. I’ve thought of a few one-time fixes, like having Perl modify the original text file, but I really just want to click the macro that imports the file each day and be done with it, so I need a way around this in Access.
You can’t keep all that information in the same field if you really need it to be set to the Date type. It has to be broken up somehow.
You can send that data through a query that breaks it up into two fields: one for the actual data and other for 'None" and blank. You can use the IsDate function to do that. Have all the queries that read the original table switch to read your new query instead.
Or
You can modify the queries that need that information to do their own conversion.
Another possibility would be to use the search & replace feature of your text editor to change all the occurences of “None” to be some date that would never naturally occur in your data (maybe 1/1/1900). Then you could programmatically test for that date for the case where “None” was a valid entry, and distinguish it from the records with no date (which would contain a NULL).
I don’t know beans from donuts about Access per se, but it sounds pretty straightforward as a database maneuver. In FileMaker I’d import the Sometimes-datefield-sometimes-“NONE” into a text field, and then I’d define a date field and run a script that entered TextToDate(TheTextField) into the date field. When it hits the values “NONE”, it’s going to leave the date field blank because there is no valid outcome of TextToDate(“NONE”).
I find myself agreeing with FatBaldGuy a lot on database issues lately.
I think cleaning up the source before importing it is your best idea. Have you considered first importing your text file (what is it, comma delimited?) into Excel? Then, you can sort or filter and change data or add columns very easily. After that, import straight into Access from the Excel file.
Anyway, that’s what I do when I have “dirty” data to get into Access somehow. Mess around with it first in Excel.
If you need to distinguish between records that have a blank date field because it has not yet been entered and those that have a blank date field because date is not relevant, you’re going to need an additional field to flag the difference.