Importing Date fields from Excel

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

I am importing a worksheet from Excel. The date columns are displayed in
mmm-yyyy format, though the data shows on the formula bar in mm/dd/yyyy
format. The Access table is formatted in mmm-yyyy, but when I import the
information and then try to build quiries by mmm-yyyy format Access continues
to recognize the mm/dd/yyyy format. So, when I join by date, Access doesn't
join two records with "Aug-2009," but instead recognizes two separate records
of "08/15/2009" and "08/01/2009."

I have tried changing the date format in the table and the spreadsheet
without success. How can I get Access to recognize only the month and the
year?

Thanks!
Pete
 
You can use a nonequi-join, if you build the query in SQL View:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Format(Table1.DateFieldName, "mmm-yyyy") =
Format(Table2.DateFieldName, "mmm-yyyy");


Or you could build a separate query for each table, including a calculated
field to give you the desired format, and then use those two queries in the
final query:

qryTable1
--------
SELECT Table1.*,
Format(Table1.DateFieldName, "mmm-yyyy") AS DateFormatField
FROM Table1;


qryTable2
--------
SELECT Table2.*,
Format(Table2.DateFieldName, "mmm-yyyy") AS DateFormatField
FROM Table2;


Final query:
 
Formats are the way the data is displayed and has no control over the storage
of that data.

It appears that the data in the Excel is stored as DateTime. Your fields in
Access should also be DateTime for dates and not a text field. Open your
table in design view and check the property of the field to see if it is text
or DateTime.

Regardless of any formating (display) all Access Datetime fields will join
correctly. Data will not match if one field has a date with time set to
midnight and the other having the same date but with time as any other than
midnight. Time other than midnight is stored as a decimal fraction with the
date integer.

A date of 7/16/2009 12:00:00 PM has a numerical value of 40010.5 whereas
the 0.5 fraction represents one half day.
 
Back
Top