Import from a text file

[Duane]

I have a text file that I import into my database on a daily basis. One of
the fields is a date of birth field. The format for this field (as it is
dowloaded to the text file) is 06-12-28. The file has approximately 1100
rows of data. The table the data is imported into has a DOB field with a
Date/Time field type.

The import works very well EXCEPT for dates prior to 1930. Every date after
1930 is imported as 06/12/1931. Dates prior to 1930 are imported as
06/12/2028.

Is there a fix to this problem? Can I do something different in the
specification?

Thank you in advance.

 

[Jeff Boyce]

Duane

If I take your description literally, you only have two values in your
database, #6/13/1931# and #6/12/2028#.

If I had data to "load" from outside sources, I'd first connect (?link) to
the data, then use a query to load it into the appropriate permanent (and
well-normalized) tables. If, in the process, I realized that I'm receiving
text values I interpret as a date (e.g., "6/12/18" would HAVE to be 1918,
because no one is yet born in 2018), I'd make sure that Access knew how to
interpret these.

You could use an IIF() statement to make sure that "x/y/18" was converted to
"x/y/1918" BEFORE adding it to the permanent table.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Duane]

Thanks Jeff,

Actually there are several fields in the text file. I import the data into
a temporary table (tbldaily). I saved the import specification, created a
macro so the process is automatic. When saving the Import Specification,
all fields were a Date/Time type. The following are the fields in my
tbldaily table; PName, PNumber, Plock, PWing, PUnit, Race, DOB,
ArrivalDate, SGTMin, SGTMax, etc. I didn't link to the Update.txt file
because I didn't know if there would be any link issues because the file is
killed and made each day.

The ArrivaDate, SGTMIN, and SGTMax are all date fields appear to be fine.
The DOB field is the one I am having problems with. It appears that Access
has an issue with dates prior to 1930. Maybe not! But I am having issues
with it.

I presently have one row of data in the text file where it looks like the
following:
617723 Doe John 102-A-1 06-12-28(DOB). Through the
Import Specification I have the field set as Date/Time, but when the data is
entered into the table is appears as 06/12/2028, not 06/12/1928.

Can you provided me a little more insight with using the IIf () function.

Thanks

 

[Guillermo_Lopez]

Are you linking to this text file, Importing the text File, or reading
the text file using VBA?
I'm assuming no one on that list has been born after the year 2000

If its the first two options, i suggest you create an Update Query to
fix all the dates less than 31. (Hint: Subtract 100 years from the
dates.)

If its the thrid option, Change it before saving it into the table
with an If-Else statement.


This day and age most system require a 4 digit year to avoid this sort
of problems. People don't usually live more than 100 years so we can
assume a lot of things by looking at the age, yet when your data
handles other type of information, then you need 4 digit years. If the
text said:
617723 Doe John 102-A-1 06-12-1928(DOB).
You wouldn't see such a problem. But I am assuming you can't change
the source of the text, so try my advices in the beginning of this
post.

Last thing. unless the name of the file changes, there shouldn't be
any link issues when they kill and recreate. (Make sure you dont
access the data when between these events)

- GL

 

[Jeff Boyce]

Access HELP -- IIF function

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Duane]

Thank you for your help.

I am importing the text file. I will use your suggestion of subtracting 100
years via an update query. It's funny how sometimes you just don't think of
the obvious.

During the import process the date of 06-12-28 in the text file is entered
into the tbldaily table as 06/12/2028.

Thanks again

Are you linking to this text file, Importing the text File, or reading
the text file using VBA?
I'm assuming no one on that list has been born after the year 2000

If its the first two options, i suggest you create an Update Query to
fix all the dates less than 31. (Hint: Subtract 100 years from the
dates.)

If its the thrid option, Change it before saving it into the table
with an If-Else statement.


This day and age most system require a 4 digit year to avoid this sort
of problems. People don't usually live more than 100 years so we can
assume a lot of things by looking at the age, yet when your data
handles other type of information, then you need 4 digit years. If the
text said:
617723 Doe John 102-A-1 06-12-1928(DOB).
You wouldn't see such a problem. But I am assuming you can't change
the source of the text, so try my advices in the beginning of this
post.

Last thing. unless the name of the file changes, there shouldn't be
any link issues when they kill and recreate. (Make sure you dont
access the data when between these events)

- GL