Can I get rid of these #num errors!?

[oliviar]

Hi guys,
I have a linked table, and it has #num errors everywhere. It is because the report I download puts "-" where a number COULD be, but isn't. I have tried creating Iif columns next to them saying 'if its an error, blank, otherwise put in the number' but that creates its own errors.


Oh, and I have seen solutions that use Excel to fix it, but I really want the fix to be in access, not excel.

 

[HiTechCoach]

If it were mine I would run an update query to replace the "-" with either 0 or Null.

 

[oliviar]

I tried that, but it produced more errors.
How do you do that so it doesn't spit the dummy?

 

[xenou]

How do you do that so it doesn't spit the dummy?

What does this mean?

Anyway: solution = don't use a linked table that mixes text and numbers. It's hopeless joining bad data to a good database, without some cleanup procedures.

 

[oliviar]

That is not a solution.
The data I am trying to input comes from a fixed source and I cannot change it. The change has to happen inside Access

Spit the dummy means to get unhappy. Like a baby throwing a fit. Its an Australianism

 

[james_lankford]

design a temp table
make the column type text instead of number for the field that's causing problems
import the excel data into the temp table
because the column type is text, the - won't cause a problem
then update the temp table, set field = null where field is '-'
then append to the real table using the temp table as the source
the text numbers will be automatically converted into numeric numbers

 

[oliviar]

Excellent, thank you.
I also assume I can leave this temp table active, as a waypoint between my originator and my final data?

 

[james_lankford]

sure, just remember to delete it's contents between runs

 

[xenou]

Spit the dummy means to get unhappy. Like a baby throwing a fit. Its an Australianism

I learned a new phrase today - never would have guessed it.
ξ