Access 2000 Import Specifications defy logic

[mark]

In Access 2000 and 2002, I have created an import specification to import the
fixed-width recordset below into an existing table. I am having strange
problems with the import of the date and time fields.

177 102003 16:43:12 102003 18:43:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
165 102003 17:43:12 102003 18:44:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
177 102003 16:41:18 102003 18:45:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
118 102003 16:41:17 102003 18:46:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
104 102000 16:29:50 102000 18:47:12 00000000 N 0000 0000 0000 0000 61930 4HGA800
130 102000 16:29:56 102000 18:43:12 00000000 N 0000 0000 0000 0000 61930 4HGA800
104 102000 16:28:56 102000 16:38:56 00000000 N 0000 0000 0000 0000 61930 4HGA800

The table design is as follows(there are no key restraints):
FLT Integer
STDATE DATE/TIME SHORTDATE
STTIME DATE/TIME LONGTIME
NDDATE DATE/TIME SHORTDATE
NDTIME DATE/TIME LONGTIME
BattID Text(9)
C Yes/No
NFO1 Text(4)
NFO2 Text(4)
NFO3 Text(4)
NFO4 Text(4)
CHGID Long Integer
VehicleID Text(9)

The import specification's Dates, Times, and Numbers selections are:
Date Delimeter = no delimeter
Time Delimeter = :
Four Digit Years = unchecked
Leading Zeros in Dates = checked
With the above values selected in the Import Spec., the STTIME and NDTIME fields
fail the import and the STDATE and NDDATE fields import correctly. If I change
the import spec. values for dates, times, and numbers to:
Date Delimeter = /
Time Delimeter = :
Then the STDATE and NDDATE fields fail the import and the STTIME and NDTIME
fields import correctly.

This makes no sense to me why the date delimeter has to be set with "/" for the
Time Delimeter to work. I have tried recreating the import spec. and everything
else I can think of and nothing seems to work. I removed the date formats on
the table as a test but it did not help. I also imported to a new table using
the import spec but the date or time fields would fail the import.

Am I missing something or is this software not going to work?
Thank you.

[Tom Wickerath]

Hi Mark,

Your message, posted 10/23/2003, is one that I marked with a flag for possible follow-up. I just
discovered the following KB article which may apply in your case:

ACC2002: Conversion Errors When You Import Dates That Have Different Formats
http://support.microsoft.com/?id=296572

The Access 2000 version is KB 208591. The STDATE field is not delimited, but the STTIME is
delimited with the colon. Likewise, the NDDATE field is not delimited, but the NDTIME field is
delimited with a colon. I believe this satisfies the criteria presented in the KB article:

SYMPTOMS
When you import a fixed-width file that has two Date fields that have different formats,
individually you can import the fields, but if you try to import the fields together, you may
receive the following error message:

Finished importing file <path> to table <name>. Not all your date was successfully imported.
Error description with associated row numbers of bad records can be found in the Microsoft Access
table '<name>_ImportErrors'.

CAUSE
You may receive this error message when one of the Date fields has no delimiters and the other
Date field is formatted as a standard Date/Time field. Access does not allow you to import a Date
field that does not have delimiters together with a Date field that does have delimiters. You
must import the non-delimited Date field as Text, and then convert the data to a standard Date
format.
Tom
______________________________________________

"mark" <[e-mail]> wrote in message news:CP********************@comcast.com...

In Access 2000 and 2002, I have created an import specification to import the
fixed-width recordset below into an existing table. I am having strange
problems with the import of the date and time fields.

177 102003 16:43:12 102003 18:43:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
165 102003 17:43:12 102003 18:44:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
177 102003 16:41:18 102003 18:45:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
118 102003 16:41:17 102003 18:46:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
104 102000 16:29:50 102000 18:47:12 00000000 N 0000 0000 0000 0000 61930 4HGA800
130 102000 16:29:56 102000 18:43:12 00000000 N 0000 0000 0000 0000 61930 4HGA800
104 102000 16:28:56 102000 16:38:56 00000000 N 0000 0000 0000 0000 61930 4HGA800

The table design is as follows(there are no key restraints):
FLT Integer
STDATE DATE/TIME SHORTDATE
STTIME DATE/TIME LONGTIME
NDDATE DATE/TIME SHORTDATE
NDTIME DATE/TIME LONGTIME
BattID Text(9)
C Yes/No
NFO1 Text(4)
NFO2 Text(4)
NFO3 Text(4)
NFO4 Text(4)
CHGID Long Integer
VehicleID Text(9)

The import specification's Dates, Times, and Numbers selections are:
Date Delimeter = no delimeter
Time Delimeter = :
Four Digit Years = unchecked
Leading Zeros in Dates = checked
With the above values selected in the Import Spec., the STTIME and NDTIME fields
fail the import and the STDATE and NDDATE fields import correctly. If I change
the import spec. values for dates, times, and numbers to:
Date Delimeter = /
Time Delimeter = :
Then the STDATE and NDDATE fields fail the import and the STTIME and NDTIME
fields import correctly.

This makes no sense to me why the date delimeter has to be set with "/" for the
Time Delimeter to work. I have tried recreating the import spec. and everything
else I can think of and nothing seems to work. I removed the date formats on
the table as a test but it did not help. I also imported to a new table using
the import spec but the date or time fields would fail the import.

Am I missing something or is this software not going to work?
Thank you.