Linking to Excel, getting #Num!

[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!

 

[gls858]

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!

Try formatting the field in Access as text.

gls858

 

[jahoobob via AccessMonster.com]

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.

 

[John W. Vinson]

The
Access data type should be a Number of size Double.

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

John W. Vinson [MVP]

 

[jahoobob via AccessMonster.com]

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.

 

[John W. Vinson]

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.

:-{(

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


John W. Vinson [MVP]

 

[gls858]

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.

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

 

[Guest]

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.

 

[Guest]

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...

 

[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.

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

 

[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.

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

gls858

 

[jahoobob via AccessMonster.com]

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

[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

 

[jahoobob via AccessMonster.com]

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.

[quoted text clipped - 16 lines]

 

[Guest]

Alas, I don't have 2007! Thanks for the help

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

gls858

 

[Guest]

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...