Linking to Excel, getting #Num!

brenda wrote:
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.

"gls858" wrote:

brenda wrote:
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


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.

gls858 wrote:
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]

John W. Vinson [MVP]

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200706/1

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

brenda wrote:
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]
at all possible I would like to keep the information in Excel instead of
importing it. Thanks!

--
Message posted via http://www.accessmonster.com

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

"gls858" wrote:

brenda wrote:
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.

"gls858" wrote:

brenda wrote:
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


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 forgot to mention that the cells formatted as zip+4 in 2003 are giving me
an error code. I may be upgrading to 2007...

"gls858" wrote:

brenda wrote:
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.

"gls858" wrote:

brenda wrote:
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


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

gls858