Excel Dates imported inconsistently

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that asked for the location of an excel file, then imports the
information from three worksheets in the file into three existing tables.
There's some validation and manipulation that happens, so I don't use
docmd.transferspreadsheet. Instead, for each worksheet, I make a preset
named range in the worksheet a adodb recordset, loop through the records and
import only the rows I want using docmd.runsql to do an insert statement.

Each sheet contains dates. I've dim-ed a variable of type date for each
date to be imported. My syntax for assigning the values in each case is:
If Not IsNull(rsXL.Fields(7).Value) Then FollowupDate =
CDate(rsXL.Fields(7).Value)

From one sheet, the dates are imported as date serials (eg: FollowupDate is
38414). From the other two sheets the dates are imported as regular dates
(eg: FollowupDate is 2/2/2005). In the excel spreadsheet, the columns
containing dates are all formatted to be date columns.

How can I make Access/Excel view all dates in a consistent manner? The way
the date is formatted affects the insert sql statement.

Thanks for your help.
 
I forgot to add - I'm using Excel 2003 and Access 2003. There is potential
for having to import Excel 2000 & 97, too, though.
 
It is possible that even when you think you have the fields formatted in
Excel correctly, sometimes they are not. Double check the cell formatting in
Excel where your records receive the Date Serial format. Also precede your
date field in your SQL statement using CDate to ensure that it comes in as a
date and will export as a date.
 
Back
Top