Importing date fields from Excel to Access

  • Thread starter Thread starter Glenn Suggs
  • Start date Start date
G

Glenn Suggs

I'm trying to import an Excel spreadsheet into Access and the sheet has a
date field in the format (example) Mar-05. In all cases the dates are
imported as m/d/yyyy. So this example becomes 3/1/2005. My question is "How
does the import determine that the day part should be 1?" Plus, this doesn't
seem to happen in every case. Sometimes the date becomes the 2nd, 3rd, or
even 23rd of the month. Can anyone explain what's happening here?
Thanks in advance,
 
Glenn,
as you are importing only the month and the year, the day of the month is
irrelevant.
However in a field of date/time data type, access stores the day, the month
and the year and makes some sort of guess about what to store for the day,
as the excel data doesn't give the day of the month.

When you want to use your imported dates in a query, form or report, you can
use the format function to show just the month and year.
here is an example you use in a calculated field in a query
Expr1:([TheDateField], "m/yyyy")
this will show the dates as 3/2005, 7/2001.
Use the query as the recordsource of a form or report.
If you wish to always see the date as 3/1/05, 4/1/07, then format the dates
in excel so that it shows the day as the first day of the month.

Jeanette Cunningham
 
Back
Top