IsNumeric

[tcheck]

I have a query where I would like to tell if a field is a numeric field. Within the query, I have a field that I run IsNumeric([FieldName]) and it returns -1 True and 0 False. Seems to work unless the 3rd position is a D or an E like the following "03E47" which returns a true statement. Any Idea how I can correct this?

[ConneXionLost]

"d" and "e" will convert the remaining portion of the number as an exponent, which is why it is returning as a "true" value. Try using InStr function to find "d" or "e" in your text string.

Otherwise you could build your own function to replace IsNumeric:

Public Function CharacterCheck(Text As String) As Boolean
    Dim i As Integer
    Dim gold As String
    gold = "1234567890"
    For i = 1 To Len(Text)
        If InStr(gold, Mid(Text, i, 1)) <> 0 Then
            CharacterCheck = False
        Else
            CharacterCheck = True
            Exit For
        End If
    Next i
End Function

[tcheck]

I ran the following in the query;

Test5: InStr("1234567890",Mid([FieldName],3,1))
With a criteria of >0 and it seemed to work.

Can you explain to me why "d" and "e" are considered exponent?

Thanks for your help, it's very much aprreciated.

[ConneXionLost]

Using VBA with Scientific Notation

Very large and very small numbers are cumbersome to write. To get around this, you can use scientific notation. Using scientific notation, you can express very large or very small numbers easily. For example, you can write the number 7,884,000,000,000,000 as 7.884E15 (notice the E in the middle of the number). Likewise, you can write the number 0.000123 as 1.23E-4.

To convert a number to scientific notation, move the decimal point so that the resulting number is between 1 and 10. After the E, specify the number of places you moved the decimal point. If you move the decimal point to the left, use a positive number after the E; if you move it to the right, use a negative number. To convert a number from scientific notation, you reverse this process--move the decimal place the number of positions indicated by the number after the E.

The way VBA expresses scientific notation depends on the data type being used. VBA uses the E when you work with a single data type and the D when you work with a double data type. Thus, 9.14E12 refers to a single-precision data type, and 9.14D12 refers to a double-precision value.

[tcheck]

Thanks again;

Your brilliance is much appreciated.