Filter on #Num!

[marley]

Hi all,

I have a linked table that returns a #Num! error from a linked Excel (not a problem).



What I want to do is run a query on that linked table to only return the row containing the #Num! error. What sql filter can I use to do this?

eg.
Linked Table------------------------------------------------------
Row 1 (formatted as text) Row 2 (formatted as number)
Text Entered Here 1 1.25
Text Entered Here 2 #Num!

Desired Query Result-----------------------------------------------
Row 1 (formatted as text) Row 2 (formatted as number)
Text Entered Here 2 #Num!

So far my filter criteria of =#Num! or ='#Num!' is not working.

Thanks

[Rawb]

What is the data type of the field containing #Num!?

If it's a String (I dont' know what else it could be really, but I guess you never know), then you should be able to just check for ="#Num!".

Not sure why that's not working for you, unless you're not using double quotes.

[marley]

The Access field is Number and is returning an error as the item brought through from the Excel is a string.

As the field is number, the error value of #Num! doesn't seem to be able to be caught in a text filter of ="#Num!". That is why I tried an expression to change this field into another format (General Number) using the IsError expression.

I have now also tried concatenating the field with the text column and filtering as a LIKE but this has not yet worked either.

Any tips?

[marley]

The data type of the Access field is Number (and is returning an error as the value being pulled through is a letter). When a filter criteria of text (e.g. ="#Num!") is passed, the error is not filtered.
I have tried concatenating the field with a text one to force a reformatting of the data and performing a LIKE"#Num!" but this has not worked yet either.

I have also tried using IsError([Row 2]) to make values returned 0 if there is no error and then filtering on the value of 0 but this has not yet worked either.

Any other tips?

----Sorry for the duplication, my computer crashed during the post

[Rawb]

Ah, if you're using a Number field, then check the contents using the IsNumeric() function like so:

SELECT * FROM tblTable WHERE Not IsNumeric([ContainsNumbers])

That should return only the Records you're looking for.

A better way to handle the Import though, would be to dump the data into a temporary Table and then copy the Records from there. That would allow you to do better error checking and give you more control over the data types of the fields.

[marley]

Thank you Rawb, that worked a treat! A big chunk of good karma is heading your way now.