Solved: Querry - isnumeric

[Philcjr]

In a querry, how do you filter out fields that are not "Numeric" and not blank?

I have tried =isnumeric with no luck

Phil

[Norie]

Phil

How did you try IsNumeric and what fields do you want to check?

[CFDM]

Cdbl([YOURFIELD])
criteria = IsNotNull

[XLGibbs]

Cdbl([YOURFIELD])
criteria = IsNotNull

Won't this only return those cells with values?

If the data type is characters/text and it contains numerical data as well as text...that gets a bit trickier. The above suggestion will return numbers, but may also return an error when it tries to convert text to number..

[Philcjr]

CFDM,
Thanks, but thats not going to achieve the results I need.

XLGibbs,
Thanks for your post, your right on track. I have a field that has text and numberic values. I want to bring in any record that is not null and is numeric.

I have tried filtering with <>IsNotNull and =IsNumeric

If it would help, I could give a sample file with dummy data as the real file has sensitive info.

[XLGibbs]

Try this in the criteria section

is not null and isNumeric([YourField])

[Philcjr]

That half worked...

Is Not Null And IsNumeric([Men's Locker]) = No Results
Is Not Null = All records with values
IsNumeric([Men's Locker]) = No Results

Thanks for your help
Phil

[XLGibbs]

That means there are no numeric values..try

isNumeric(cDbl([Men's Locker]))


Since a null would return false for isNumeric, you do not need the "is not null" part

[Philcjr]

I get an error. "Invalid use of Null" :(

[XLGibbs]

Also, I would have to look it up, but I know in most SQL languages it would have to be

isNumeric([Men's Locker]) = 1

since excel automatically deduces or assumes a true as the desired result. In SQL you have to identify the target result of true(1) or false(0)

[XLGibbs]

I get an error. "Invalid use of Null" :(


hmm, probably the error I thought you would get by cDbl against text.

Maybe,

is not null and isNumeric([Men's Locker]) = 1

[Philcjr]

isNumeric([Men's Locker]) = "1" (Result = Nothing)
isNumeric([Men's Locker]) = "0" (Result = Blanks and Text)

[XLGibbs]

isNumeric([Men's Locker]) = "1" (Result = Nothing)
isNumeric([Men's Locker]) = "0" (Result = Blanks and Text)

are the text fields you are looking for with numbers ALL numbers?

If they are A123 for example, isNumeric won't work..

what is an example of the field you are trying to get? Are there any leading or trailing spaces?

[Philcjr]

XLGibs,

Thank you for your time, I have scrubbed my Mother's work database so the field now only has numerical values.

Thanks again.... Phil from NJ