A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

#Num! in linked Excel file



 
 
Thread Tools Display Modes
  #1  
Old April 27th, 2004, 04:39 PM
Dkline
external usenet poster
 
Posts: n/a
Default #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  
Old April 27th, 2004, 06:30 PM
Roger Carlson
external usenet poster
 
Posts: n/a
Default #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  
Old April 27th, 2004, 06:57 PM
Lynn Trapp
external usenet poster
 
Posts: n/a
Default #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  
Old April 27th, 2004, 07:34 PM
Roger Carlson
external usenet poster
 
Posts: n/a
Default #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  
Old April 27th, 2004, 08:57 PM
Dkline
external usenet poster
 
Posts: n/a
Default #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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:56 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.