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
|
|||
|
|||
#Num! in linked Excel file
I have a linked Excel file. Within that file is a field/column of numbers.
When I look at it in Excel - I see the numbers. When I look at in Access - I see #Num! When I see it in the Form, I don't - the field is blank. What is the #Num! telling me - that it is not a number? If it's a number in Excel, why wouldn't Access see it as a number. The numbers are not overly large. Range is 1,000,000 to 100,000,000. |
#2
|
|||
|
|||
#Num! in linked Excel file
Is the column mostly blank? With this number appearing a hundred or more
records down? If so, it is a problem that I have experienced with linked Excel tables too. It seems that when it is linked, Access looks at the first hundred or so records to see what data type it is. If it finds no values, it makes it Text. If it subsequently runs into numbers after it is linked, it displays #NUM. I don't have any real good solution for this. You can't change the datatype after linking, and you can't define it as number during the linking. (At least I've not found a way to do so.) The only solution I have found is to physically move a whole row that has a value in this column to the top of the sheet. Then it will read the datatype correctly. If anyone has a better solution, I'd love to hear it. -- --Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org "Dkline" wrote in message ... I have a linked Excel file. Within that file is a field/column of numbers. When I look at it in Excel - I see the numbers. When I look at in Access - I see #Num! When I see it in the Form, I don't - the field is blank. What is the #Num! telling me - that it is not a number? If it's a number in Excel, why wouldn't Access see it as a number. The numbers are not overly large. Range is 1,000,000 to 100,000,000. |
#3
|
|||
|
|||
#Num! in linked Excel file
Roger,
If memory serves me correctly, Access actually searches the first 10 or 15 rows to determine a data type on a link or import. Thus, if it finds text in ANY of those test rows it will make the column a text datatype. I don't know for sure how it handles NULLs in those first rows, but probably as you suggest. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Roger Carlson" wrote in message ... Is the column mostly blank? With this number appearing a hundred or more records down? If so, it is a problem that I have experienced with linked Excel tables too. It seems that when it is linked, Access looks at the first hundred or so records to see what data type it is. If it finds no values, it makes it Text. If it subsequently runs into numbers after it is linked, it displays #NUM. I don't have any real good solution for this. You can't change the datatype after linking, and you can't define it as number during the linking. (At least I've not found a way to do so.) The only solution I have found is to physically move a whole row that has a value in this column to the top of the sheet. Then it will read the datatype correctly. If anyone has a better solution, I'd love to hear it. -- --Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org "Dkline" wrote in message ... I have a linked Excel file. Within that file is a field/column of numbers. When I look at it in Excel - I see the numbers. When I look at in Access - I see #Num! When I see it in the Form, I don't - the field is blank. What is the #Num! telling me - that it is not a number? If it's a number in Excel, why wouldn't Access see it as a number. The numbers are not overly large. Range is 1,000,000 to 100,000,000. |
#4
|
|||
|
|||
#Num! in linked Excel file
You may well be correct. Every time I had this problem the first non-null
record much farther down. An other thing I tried to correct this problem was to move a record to the top, link the table, then move the record back to it's original spot. No good! The same thing happens. Apparently everytime the database is opened, it re-defines the datatype of the Linked sheet. Maddening! -- --Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org "Lynn Trapp" wrote in message ... Roger, If memory serves me correctly, Access actually searches the first 10 or 15 rows to determine a data type on a link or import. Thus, if it finds text in ANY of those test rows it will make the column a text datatype. I don't know for sure how it handles NULLs in those first rows, but probably as you suggest. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Roger Carlson" wrote in message ... Is the column mostly blank? With this number appearing a hundred or more records down? If so, it is a problem that I have experienced with linked Excel tables too. It seems that when it is linked, Access looks at the first hundred or so records to see what data type it is. If it finds no values, it makes it Text. If it subsequently runs into numbers after it is linked, it displays #NUM. I don't have any real good solution for this. You can't change the datatype after linking, and you can't define it as number during the linking. (At least I've not found a way to do so.) The only solution I have found is to physically move a whole row that has a value in this column to the top of the sheet. Then it will read the datatype correctly. If anyone has a better solution, I'd love to hear it. -- --Roger Carlson www.rogersaccesslibrary.com Reply to: Roger dot Carlson at Spectrum-Health dot Org "Dkline" wrote in message ... I have a linked Excel file. Within that file is a field/column of numbers. When I look at it in Excel - I see the numbers. When I look at in Access - I see #Num! When I see it in the Form, I don't - the field is blank. What is the #Num! telling me - that it is not a number? If it's a number in Excel, why wouldn't Access see it as a number. The numbers are not overly large. Range is 1,000,000 to 100,000,000. |
#5
|
|||
|
|||
#Num! in linked Excel file
Thank you to both for the solution.
The last thing my macro did was to sort the Excel file on the key field. This had the net effect of putting ALL the records without a value in that column at the top. I remmed out the sort, reran it, and voila! Numbers! The field in row 1 had a number so all the rest were numbers in the records that had a number in the field. As the macro is processing, for those records in which I know this field should NOT have a value, I am setting the value to "". Should I be using something else? Is there a value I can assign that equates to NULL in Access? So again thank you. Next concen though is - is there no way to tell Access that this column in the linked spreadsheet is a $ amount or currency? Something that can be done by macro or VBA? |
Thread Tools | |
Display Modes | |
|
|