Help please! Excel -> Access Link #Num! Error

[Walshie1987]

I've got a spreadsheet with different prices and percentages etc. in that I want to link to Access, I've gone through the wizard as I always do and most of the data has imported fine.

But, there are one or two cells that when opened in Access, just give the error "#Num!" they are in the same format as the other cells, nothings different at all.

Cannot figure this one out! Any ideas?

 

[SkipVought]

Maybe providing some additional info might help.

Are these #NUM! values in the same column?

What are the actual values (not what is displayed, but the FORMAT BAR value) in these two cells?

What are some typical VALUES in the cells that imported correctly?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Walshie1987]

Thanks for the reply, there are various cells displaying the #Num! error, no just in the same column or row. It is a montly report I am trying to link the data from, so the only thins that changes are the values in the cells, not the format.

Some typical values are, for example,

October November
Item Cost % Increase Item Cost % Increase
£100.00 0% £110 10%

The item cost value from November is showing fine, but in octobers it's showing #Num!

Hope that was clear!

Cheers

 

[xlbo]

October's has 2 decimals. November's has none

Check the data type definition of your fields in Access. If they are INT, then a decimal won't flow through...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Walshie1987]

Sorry my fault, the £100 should be £100.00

 

[SkipVought]

Are you saying that the VALUES IN THE FORMULA BAR are...
[tt]
£100.00 0% £110 10%
[/tt]
Then get rid of the £ and %. You have TEXT, not NUMERIC values.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[combo]

See explanation here.

combo

 

[Walshie1987]

Thanks!

 

[ciarra41]

This do work but if you append the query to a new table the values are now null. Is there a way to do this in Access instead of excel because I'm appending the excel spreadsheet into a new table. But the #Num values are being appended as null. Again I'm using the code in excel but I would like to this in Access.

Sub Addspace()
Dim cell As Object

For Each cell In Selection
cell.Value = " " & cell.Value
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Next

End Sub

 

[SkipVought]

ciarra41,

Please post your question in a new thread.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}