ISNUMERIC Function With Text Field

[deekadelic]

I have a field with a TEXT data type, though most of the values in the field are composed entirely of numbers. I am trying to isolate these values with the ISNUMERIC function, so that I can edit them; but the function is coming up with no records.



Am I missing something?

[ssanfu]

Not sure how you are using the function..... from Help:
IsNumeric returns True if the entire expression is recognized as a number; otherwise, it returns False.

IsNumeric("123 Front St.") = False
IsNumeric("123.223") = True

[deekadelic]

I just noticed that for some reason, when I used the criterion of ISNUMERIC(BLAH) = "True", the numeric values produced FALSE in every record, but when I used ISNUMERIC(BLAH) = -1, they were all TRUE.

Seems odd that Excel would be making that kind of distinction.

[ssanfu]

"True" (in quotes) is a text string.

So

ISNUMERIC(BLAH) = "True"

(True in quotes) will always returns FALSE (0) because you are comparing a Boolean value to a text string. They will never be equal.



In Excel, TRUE is defined (declared) as (equal to) 1
In Access,TRUE is defined(declared) as (equal to) -1
Only M$ knows why the difference in the TRUE values..

[deekadelic]

That's what I was thinking, but for some reason Access adds the quotes around the word TRUE, and won't let me delete them.

[davegri]

Look at "hours" in this grid. Hours is a text field and the isnumeric column works just as expected.
The SQL view shows:

SELECT qVolTasks.IFIRST, qVolTasks.ILAST, qVolTasks.TaskDescription, qVolTasks.StartDate, qVolTasks.EndDate, qVolTasks.Hours
FROM [qDBTCMAST ALPHA] INNER JOIN qVolTasks ON [qDBTCMAST ALPHA].TMID = qVolTasks.TMID
WHERE (((IsNumeric([Hours]))=True));

Attachment 25197