data was messed up after importing from Excel to Access 2000 datab

[Guest]

I am having a very confusing time with a Access 2000 database. I imported
some old data from Access 97 to Access 2000 using Excel 2000. The import
into Access 2000 appeared to work successfully. However now when I view the
data or add new data to the table where the imported data was loaded the date
in a particular column gets confused. Normally, I store the date information
in the form of dd-mon-yy (or day, month, year) such as 10-Oct-06, which
should be the 10 of October, 2006. In my case the date was exported from a
previous Access 97 database as month, day, year so the exported and imported
data was displayed as 10 6 2006 or October 6, 2006 for the date column in
question. Now when I enter a date into the table, the date will mess up such
as 6-Jun_06, rather than the correct date of 6-Oct-06. It seems as if the
day and the month are mixed up in displaying new information in my date
column of the imported table.

What would have caused Access 2000 to reverse the dates in a column when new
information is entered, yet keep the dates correct in the rows and columns
that were exported. This particular table (called ATS Lines 2006 in my case)
is used in a lot of reports as a sub report table and thus the information
can be messed up in several reports.

And as I have said before this date problem only affects new values or new
records, not old data already loaded in this particular table after the
import.

Edward Letendre

 

[Jeff Boyce]

Edward

Access stored date/time type data as a numeric value, with the integer
portion representing the number of days since a date late in the 1800s, and
the decimal portion representing the portion of one day (e.g., .5 = noon).
The description you gave may be interpreted as a date, but if you are
actually storing the text you provided, Access can't do its date/time
arithmetic on that.

What is the data type?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

I believe that the date columns (in my case, date entered into the system and
date the line (or part of the project was completed) were stored as medium
date with a funny key or mask for the date. I cannot remember the mask at
the moment as I am not at the computer that had the database, but the dates
are thus all stored as dates such as 10-Oct_06.

Edward Letendre

 

[Jeff Boyce]

Edward

Dates are not "stored as medium date". Dates are stored as a number.
Date/time values can be formatted, but that only changes what is displayed,
not what is stored.

Have you tried running a query against the date/time field and changing the
format?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/