#Num! in linked Excel table revisited

[Dick Watson]

I'm linking to an Excel file the contents of which are not mine to muck
with. I'm also doing this on a recurring basis, so importing all of the data
and polishing it continually is not a great solution. Since this is somebody
else's idea of a "database" in Excel, it has the usual assortment of mangled
data typing including text in fields the Access link detects (generally
correctly so) as numeric or date/time. Every solution I can find in the
archives pretty much comes down to fix the Excel or import it all as text
(programmatically, if necessary) and then fix the data in Access.

For these reasons, I was trying to create a query to clean up the problem
children and then base everything else off that query rather than off the
underlying linked table.

If I set a criteria of Is Null on one of the fields with problematic data,
the #Num! records are in the result.

If I try to create a calculated field of
IIf(IsNull([ProblemField]),Null,[ProblemField]) I still get #Num! falling
through. Likewise for IsNumeric().

So, my question is how can I clean the #Num! data up in a calculated field?

 

[Van T. Dinh]

I am not sure what the wrong with your problem "children"
(mixed column is always a problem child in linking Excel
to Access). However, if Access receives garbage
(according to Access standards), not much Access can do
except for spitting out garbage also.

Thus, the only solution is to feed Access what Access can
accept according to Access standards.

HTH
Van T. Dinh
MVP (Access)

 

[Dick Watson]

Criteria for Is Null and Is Not Null do a fine job for spotting these.
Expressions for IIf(IsNull([FieldWProblems])) do not. Why?

As noted, feeding Access what Access can accept is not an easy option since
I don't control and can't change the source data.

 

[Kelvin]

You also need to check for "" not just Null. When someone erases a cell in
Excel it doesn't turn it into a Null but a "". Try adding this check into
your IIF and see if that helps.

IIf(IsNull([ProblemField]) or [ProblemField]="",Null,[ProblemField])

Kelvin

 

[Dick Watson]

Thanks for the thought--it appears that Access converts these cells to a
Date/Time Null and this construction has type errors against the linked
Date/Time.