If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
What is #Num! error
I am trying to link an EXCEL sheet to ACCESS. I think that I have a format
problem with some of my numbers but I do not know how to create a column that can have both text and numbers, and the numbers can still be sorted. Most of the numbers in the column are o.k. but any new ones I have added recently do not appear. Instead I get #Num! Do I need to set the format in EXCEL and should it be "General" or "Text"? |
#2
|
|||
|
|||
What is #Num! error
When you link to an EXCEL spreadsheet from ACCESS, Jet (the database engine)
reviews the first 8 to 25 rows of data in the spreadsheet and decides what the data type is. If there are no nonnumeric characters in those initial rows, Jet will assign a numeric data type. Then, for rows farther down with nonumeric characters, ACCESS will display the #Num! error because those strings are not numeric. With linking, your have two choices involving changes to the EXCEL spreadsheet: 1) Put nonumeric characters in the first row of the spreadsheet. 2) Put an ' character in front of every value in the appropriate column for the spreadsheet. That tells Jet that the value is a text value and not a numeric value. You can make changes to the Registry itself that will force Jet to scan all the rows before deciding on a data type. See this article for information about how to change the MaxScanRows property to the value of 0: http://www.dicks-blog.com/excel/2004...al_data_m.html -- Ken Snell MS ACCESS MVP "Brenda" wrote in message ... I am trying to link an EXCEL sheet to ACCESS. I think that I have a format problem with some of my numbers but I do not know how to create a column that can have both text and numbers, and the numbers can still be sorted. Most of the numbers in the column are o.k. but any new ones I have added recently do not appear. Instead I get #Num! Do I need to set the format in EXCEL and should it be "General" or "Text"? |
#3
|
|||
|
|||
What is #Num! error
Thank you very much. I was getting to this solution, but using "format
painter" but I think that the ' solution is much better. Thank you! Brenda "Ken Snell (MVP)" wrote: When you link to an EXCEL spreadsheet from ACCESS, Jet (the database engine) reviews the first 8 to 25 rows of data in the spreadsheet and decides what the data type is. If there are no nonnumeric characters in those initial rows, Jet will assign a numeric data type. Then, for rows farther down with nonumeric characters, ACCESS will display the #Num! error because those strings are not numeric. With linking, your have two choices involving changes to the EXCEL spreadsheet: 1) Put nonumeric characters in the first row of the spreadsheet. 2) Put an ' character in front of every value in the appropriate column for the spreadsheet. That tells Jet that the value is a text value and not a numeric value. You can make changes to the Registry itself that will force Jet to scan all the rows before deciding on a data type. See this article for information about how to change the MaxScanRows property to the value of 0: http://www.dicks-blog.com/excel/2004...al_data_m.html -- Ken Snell MS ACCESS MVP "Brenda" wrote in message ... I am trying to link an EXCEL sheet to ACCESS. I think that I have a format problem with some of my numbers but I do not know how to create a column that can have both text and numbers, and the numbers can still be sorted. Most of the numbers in the column are o.k. but any new ones I have added recently do not appear. Instead I get #Num! Do I need to set the format in EXCEL and should it be "General" or "Text"? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Reserved Error - 1104 - Works in 97 not in XP? | beveritt | General Discussion | 0 | August 25th, 2005 05:07 PM |
Error message different in MDB and MDE | david epsom dot com dot au | General Discussion | 1 | September 21st, 2004 12:47 AM |
Continual Error 1321 Trying to Install Office 2003 | Chad Harris | General Discussions | 9 | June 11th, 2004 08:19 AM |
Product Key for Office XP | P.G.Indiana | Setup, Installing & Configuration | 1 | June 7th, 2004 03:22 AM |
Error #1321 MOS 2003 Setup | Chad Harris | Setup, Installing & Configuration | 1 | June 7th, 2004 12:22 AM |