"Or" option using INSTR() Function?

[Lou]

I would like to use the INSTR()function to give me the
position of the first non-numeric character in a string.
The way that I understand the function to currently work,
you must provide a specific character: Example--
INSTR(1,[strname],"A",1) finds the position of the
letter "A". I would like to have the function
locate "whatever" the non-numeric character may be.
Am I going about this the wrong way?
Thanking you in advance!

 

[fredg]

I would like to use the INSTR()function to give me the
position of the first non-numeric character in a string.
The way that I understand the function to currently work,
you must provide a specific character: Example--
INSTR(1,[strname],"A",1) finds the position of the
letter "A". I would like to have the function
locate "whatever" the non-numeric character may be.
Am I going about this the wrong way?
Thanking you in advance!

I'll assume a "non-numeric' character is a printable value, not a
non-printable value (such as a line feed or a carriage return
character).
The Asc() value of numbers 0 through 9 is 48 through 57.
Printable values start at chr(58) ":"
You'll need to write your own User Defined Function to find a value
greater than 57.

Place this funtion in a module.
Air code: (Air Code)

Public Function NonNumber(FieldIn as String)
Dim intX As Integer
Dim intY As Integer
For intX = 1 To Len(FieldIn)
intY = Asc(Mid(FieldIn, intX, 1))
If intY > 57 Then
NonNumber = intX
Exit Function
End If
Next intX
NonNumber = 0 ' No non-number found
End Function

You'll need to add whatever error handling you might need.

Call the function from any control:
=NonNumber([FieldName])
Or from a query:
Exp:NonNumber([FieldName])

 

[david epsom dot com dot au]

For general grepping, you can reference the RegEx object.
However in this case you MAY be able to find the numeric
part by using VAL, and then split it off the front by using
RIGHT/LEFT

(david)

 

[Lou]

-----Original Message-----

I would like to use the INSTR()function to give me the
position of the first non-numeric character in a string.
The way that I understand the function to currently work,
you must provide a specific character: Example--
INSTR(1,[strname],"A",1) finds the position of the
letter "A". I would like to have the function
locate "whatever" the non-numeric character may be.
Am I going about this the wrong way?
Thanking you in advance!

I'll assume a "non-numeric' character is a printable value, not a
non-printable value (such as a line feed or a carriage return
character).
The Asc() value of numbers 0 through 9 is 48 through 57.
Printable values start at chr(58) ":"
You'll need to write your own User Defined Function to find a value
greater than 57.

Place this funtion in a module.
Air code: (Air Code)

Public Function NonNumber(FieldIn as String)
Dim intX As Integer
Dim intY As Integer
For intX = 1 To Len(FieldIn)
intY = Asc(Mid(FieldIn, intX, 1))
If intY > 57 Then
NonNumber = intX
Exit Function
End If
Next intX
NonNumber = 0 ' No non-number found
End Function

You'll need to add whatever error handling you might need.

Call the function from any control:
=NonNumber([FieldName])
Or from a query:
Exp:NonNumber([FieldName])

 

[MGFoster]

I would like to use the INSTR()function to give me the
position of the first non-numeric character in a string.
The way that I understand the function to currently work,
you must provide a specific character: Example--
INSTR(1,[strname],"A",1) finds the position of the
letter "A". I would like to have the function
locate "whatever" the non-numeric character may be.
Am I going about this the wrong way?
Thanking you in advance!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

InStr() won't do that. You'll have to use brute-force method. Below
is a little function I whipped up for fun.


Public Function TypeInstr(strSearch As String, strType As String) _
As Integer
' Purpose:
' Find the first occurence of the indicated data type in the
' indicated string.
' In:
' strSearch The indicated string
' strType The name of the data type: String or Number
' Out:
' The index in the strSearch where the 1st occurence of the
' the data type was found.
' Zero if not found. (Default)
' Created:
' mgf 18mar2004
' Modified:
'

Dim i As Integer
Dim c As String
Dim fNumberSearch As Boolean

If Len(strSearch) > 0 Then

fNumberSearch = (strType = "Number")

For i = 1 To Len(strSearch)
c = Mid$(strSearch, i, 1)
If (fNumberSearch And IsNumeric(c)) _
Or (Not fNumberSearch And Not IsNumeric(c)) Then
TypeInstr = i
Exit For
End If
Next i

End If

End Function

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFongYechKqOuFEgEQLTgQCfT1ESYVmlM+uBjaaQqgE1HxHUC9YAoMnb
NY4euN4YRI00CHWcsDaVr2/a
=K0YK
-----END PGP SIGNATURE-----
--