L
LJgrnl
Apologies for cross-posting, I'm not sure which forum is better for this
problem.
In an Access database, 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.
I'm using Access 2003 and Excel 2003, but may have to do the same with excel
2000 & 97.
Thanks for your help.
problem.
In an Access database, 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.
I'm using Access 2003 and Excel 2003, but may have to do the same with excel
2000 & 97.
Thanks for your help.