#Num! when Importing linked table

[xluserg]

Hi all

New to Access 2010.

I'm trying to do a link to the data source by creating a linked table in excel 2010.

Column N was formmated as GeneraI and I got #Num! I have formmated Column N as text but still get the error
#Num!

#Num! is shown for cells that have 4½ shd 3¾ etc

Other cells in the column have numbers 10 31 7 etc.

How can I get around this error of importing this data.

Using Office 2010

Thanks in advance

Graham

 

[Joe4]

The problem with importing Excel files into Access, and Excel and Access try to talk to each other, and Access "guesses" what the format of each field is (doesn't really matter how you have the column formatted in Excel), and they sometimes guess wrong. I think it looks at something like the first 10 rows of data in determining what the format should be. So if you data below row 10 looks different than the first 10 rows of data (as far as if it looks like numbers or text), it could be problematic.

One way to get around it is to export your Excel file to a text file, and then import the text file into Access, where you have control over the format of each field through the Import Wizard.

 

[xluserg]

Hi Joe

Thanks for your reply.

Exporting to a text file seems strange when you've Excel and Access, But that may be needed in some cases.

I decided to try this first.

ACC2000: #Num Appears in Linked Microsoft Excel Spreadsheet

It works but striped a number from some of the cells. So I tried this....

I used Concatenate to put some text before the values in the cells. result was "Btn 3¾"

Imported linked table from Access and its seems to work fine.

Thanks for the help Joe

Have a great day

Graham

 

[Joe4]

Glad you got it to work out.

Exporting to a text file seems strange when you've Excel and Access, But that may be needed in some cases.

I agree, it is odd, but it works.

One of my biggest complaints about the Microsoft programs is that I think sometimes they go overboard in trying to make things too user friendly/easy to use by assuming too much and let the software programs figure out what you are trying to do instead of allowing you to tell it what to do. Unfortunately, this Excel to Access connection is one of these instances. You really have very little control over how Access interprets Excel data. So changing to a text file gives the control back to you.

Thankfully, a lot of the time it isn't necessary, as the two programs often "get it right" (a lot depends on the data), but for those instances in which you just can't get it to work out, it is good to have a few tricks up your sleeve...

 

[xluserg]

I certainly agree with you.

I did find that Access did'nt like "Btn 0" for some reason, I'll let you know how I get on sorting that out.

Graham