Table link to Excel File - #Num! problem

[Guest]

Hello:

I have a table in Excel. I do not maintain it. Those who do are only
comfortable with Excel.

I need to slice it up a bit in queries. Table has about 7 fields, some are
text some are numbers and dates.

One specific field is for numbers only. In it, not all the records were
filled out, b/c it's an optional field. But, when a record does not have a
null in that field, it is a number - they are all numbers. So, in that field,
there are either nulls or numbers.

Access (2002) will not display that field's entries. It shoots out either
nulls, or #Num! in place of the numbers. I formated the field in Excel to
"number", it did not help.

A second field with dates has the same problem.

Does anyone know how come and how to fix this? I've seen this countless
times. I really want to avoid the extra step of importing, b/c I'm dealing
with relatively non-technical people, whose comfort is in Excel.

Thanks,
Robert (pepe)

 

[Roger Carlson]

This has been a perenniel problem between Access and Excel. The problem is
the import wizard reads the first few records in the Excel worksheet to
determine the data type. You can't change it and formatting it in Excel
won't do anything.

I've used two solutions, neither of which is ideal:
1) Create an Access table with the data types you want. Save the Excel file
as a CSV and import the data as text data and append the data to table,
converting datatypes as necessary.
2) Make sure your first line in the Excel file has the data of the correct
datatype in it. Sometimes I've created a false line at the top, with some
criteria that I use to exclude that record in database queries.

This problem has supposedly been fixed in Access 2007. (I say supposedly
only because I haven't tested it yet.) You're supposed to be able to define
the datatype in Access in 2007.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Ken Snell \(MVP\)]

It's possible to force Jet to scan all the rows and not guess the data type
based on just the first few rows. See this article for information about the
registry key (see TypeGuessRows and MaxScanRows information):
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

 

[Guest]

Thank you

It's possible to force Jet to scan all the rows and not guess the data type
based on just the first few rows. See this article for information about the
registry key (see TypeGuessRows and MaxScanRows information):
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

 

[Guest]

Thank you

This has been a perenniel problem between Access and Excel. The problem is
the import wizard reads the first few records in the Excel worksheet to
determine the data type. You can't change it and formatting it in Excel
won't do anything.

I've used two solutions, neither of which is ideal:
1) Create an Access table with the data types you want. Save the Excel file
as a CSV and import the data as text data and append the data to table,
converting datatypes as necessary.
2) Make sure your first line in the Excel file has the data of the correct
datatype in it. Sometimes I've created a false line at the top, with some
criteria that I use to exclude that record in database queries.

This problem has supposedly been fixed in Access 2007. (I say supposedly
only because I haven't tested it yet.) You're supposed to be able to define
the datatype in Access in 2007.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L