Problem importing into Access 2000 from Excel 2003

[carl.strandberg]

Does anyone have any ideas as to why this is happening?

Background: I have this database that imports an spreadsheet into a
table. One of the columns has either dates
or a NULL (as in the word null is actually entered) value.
Historically (over a year now), this field has
been importing into an Access table as a text field, from which some
queries feed off of it.

Problem: The field is now being imported as a Date/Time field and the
NULL values from the Excel spreadsheet
are being imported as true nulls (blanks). This, of course, messes up
my queries.

Changes: This is only happening on our new machines which have Windows
XP. Recently, we upgraded some
of our PCs to Windows XP from Windows 2000 Pro.

Actions I've tried: The DB is in Access 2000 and the spreadsheet is
Excel 2003. When I run it on a Windows 2000
machine, it works perfectly. When I run it on the XP machine, it has
blank values in the table.
I use a module to run the import (DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, "CURR_MNTH", strPathFileName, True). I
checked the references
between the 2 machines and they are the same.

Is there a simple setting I'm missing or something?

 

[Douglas J. Steele]

How does it mess up your queries? Can you not exclude the rows with Null
values (using IS NOT NULL as a criteria), or use the Nz function to convert
them to something you can handle?

 

[Jeff Boyce]

I can't be sure from your description, but it sounds like you may be
importing Excel directly into an Access table. If so, when you do that,
Access has to try to guess what the data types are.

Instead, create a table with the exact data types you need for the fields.

Link to (rather than import) the Excel spreadsheet.

Create one/more queries that append records from the spreadsheet (linked) to
your more permanend data structure ... and you can explicitly coerce some
data into the data types needed.

Regards

Jeff Boyce
Microsoft Office/Access MVP