Linking to Excel, getting #Num!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to be able to keep a database in Excel, but link it to Access. I have
done this, and everything is fine except in the zip code field and some phone
numbers. In my Excel spreadsheet the zip code field is formatted as
special/zip code+4. Most of these show up on the Access table as #Num! If
at all possible I would like to keep the information in Excel instead of
importing it. Thanks!
 
brenda said:
I need to be able to keep a database in Excel, but link it to Access. I have
done this, and everything is fine except in the zip code field and some phone
numbers. In my Excel spreadsheet the zip code field is formatted as
special/zip code+4. Most of these show up on the Access table as #Num! If
at all possible I would like to keep the information in Excel instead of
importing it. Thanks!
Click to expand...

Try formatting the field in Access as text.

gls858
 
Switch to Access!
The reason is that and excel database is flat.
If you have a student db in excel you have to store the a class name,
teacher's name, room #, for each subject a student takes. In Access you can
store the class name, teacher's name, room # once and have an single ID for
the student's first period class, second period class, etc. As a matter of
fact, you wouldn't even store the class schedule for the students in the
student's info table.
Move you db to Access.

That being said, the #Num indicates that the Data types aren't matching. The
Access data type should be a Number of size Double. You might want to place
00000-0000 in the format block also.
 
The
Access data type should be a Number of size Double.
Click to expand...

Bob, might it not be better to use a Text datatype? You'll never be doing math
with a zipcode!

John W. Vinson [MVP]
 
An Excel ZIP+4 converts to a double precision number when you import it into
Access so I figured that it would link the same way. It also acts like a 9
digit integer in Excel when you use it in an arithmetic operation.
 
An Excel ZIP+4 converts to a double precision number when you import it into
Access so I figured that it would link the same way. It also acts like a 9
digit integer in Excel when you use it in an arithmetic operation.
Click to expand...

:-{(

Thanks. Another of the many quirky incompatiblities between the two programs!


John W. Vinson [MVP]
 
jahoobob said:
An Excel ZIP+4 converts to a double precision number when you import it into
Access so I figured that it would link the same way. It also acts like a 9
digit integer in Excel when you use it in an arithmetic operation.
Click to expand...

I guess I'm missing something. When would you ever use a zip code
in an arithmetic function? I tested an import of a cell formatted
as zip+4 in Excel to a text field in Access and it works fine.

gls858
 
How do I format the field in Access if the table is an Excel link? It won't
let me change properties in the table.
 
Is switching to Access my only option? It would make life a lot easier if I
could just link to the constantly updating Excel file...
 
brenda said:
How do I format the field in Access if the table is an Excel link? It won't
let me change properties in the table.
Click to expand...

My mistake. I imported than data rather than linking it. Looks
like the only way would be to change the format on the Excel
spreadsheet.

gls858
 
brenda said:
How do I format the field in Access if the table is an Excel link? It won't
let me change properties in the table.
Click to expand...

One last comment. In Access 2007 the linked Excel sheet with
cells formated as zip+4 seem to display the zip codes properly.

gls858
 
I wouldn't use it in an arithmetic function, I am only reporting what Excel
does and attempting to answer the original poster's question.
An Excel ZIP+4 converts to a double precision number when you import it into
Access so I figured that it would link the same way. It also acts like a 9
Click to expand...
[quoted text clipped - 6 lines]
I guess I'm missing something. When would you ever use a zip code
in an arithmetic function? I tested an import of a cell formatted
as zip+4 in Excel to a text field in Access and it works fine.

gls858
Click to expand...
 
As long as you are using Access and it is fairly easy to import your Excel
sheet(s) into Access I would say it is your best option unless you use Excel
to do calculations that Access can't do easily. If people like to enter data
into a spreadsheet format you can use a form with a datasheet view
Is switching to Access my only option? It would make life a lot easier if I
could just link to the constantly updating Excel file...
Switch to Access!
The reason is that and excel database is flat.
Click to expand...
[quoted text clipped - 16 lines]
Click to expand...
 
Alas, I don't have 2007! Thanks for the help

gls858 said:
One last comment. In Access 2007 the linked Excel sheet with
cells formated as zip+4 seem to display the zip codes properly.

gls858
Click to expand...
 
I forgot to mention that the cells formatted as zip+4 in 2003 are giving me
an error code. I may be upgrading to 2007...
 
Back
Top