Testing Whether a Field Has Only Number Values

Testing whether a field has a number value is done differently in FileMaker than it is in ClarisWorks. What works in one will not work in the other.

In FileMaker:
To find out if a field has only a numerical value entered can be done with the Length, TextToNum, and NumToText functions.

A calculation with an If statement would look like this:

NumberOnlyTest (Calculation, Text Result)
= If (Length (NumToText (TextToNum (TestField))) = Length (TestField) , "" , "Value has a text character in it. Please change to only numbers.")

Where TestField is the field you are testing.

In ClarisWorks:
To find out if a field has only a numerical value entered, we test if the result of the TEXTTONUM function when evaluated as a number (ISNUMBER) returns an error. If the result is an error the field does not contain a number. Example:

ISERROR(ISNUMBER(TEXTTONUM('TestField')))

Then we check for the length of the converted value being the same length as the original value. Example:

TEXTTONUM(NUMTOTEXT('TestField'))<>'TestField'

Then, putting both of these together inside a nested If statement would look like this:

NumberOnlyTest (Calculation, Text Result)
= IF(ISERROR(ISNUMBER(TEXTTONUM('TestField'))),"Value contains a non-numeric character", IF(TEXTTONUM(NUMTOTEXT('TestField'))<>'TestField',"Value contains a non-numeric character",""))

When the TEXTTONUM function does not produce a number then the ISNUMBER function produces an error, which flags the value as not being a number. If there is both number and non-number data in the field, the comparison test flags the field as having a non-number character in it.

NOTE: The two tests must be done sequentially (i.e., in a nested fashion) as the ISERROR function will not work with an OR conjunction when used inside an IF test.