Find First Number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I have a description field in a table that contains both numbers and
text. I wrote a query with Like "*#*" in the criteria for that field so I
know that all rows returned have at least one number in that field.

Now I would like to return the first number in that field. For example, if
there is a row with AA1A2A, I would like to have 1 returned. If it is
A21AAA, I would like to have 2 returned.

Is this possible?

Thanks in advance for any help.
 
I wonder if you might use Value([YourField]) to return the first numeric
value, then sort on that field?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Put the function below in a standard module.
Create a Calculated field in your query that calls the function passing the
field you want to check to the function. It will return the first character
in the field that is a number.

In the query:

TheNumber: FirstNumber([MyFieldName])

The function:

Public Function FirstNumber(strString As String) As Integer
Dim lngCtr As Long
Dim strTheChar As String

For lngCtr = 1 To Len(strString)
strTheChar = (Mid(strString, lngCtr, 1))
If IsNumeric(strTheChar) Then
FirstNumber = Cint(strTheChar)
Exit For
End If
Next lngCtr
End Function
 
Thanks! It worked great.

Klatuu said:
Put the function below in a standard module.
Create a Calculated field in your query that calls the function passing the
field you want to check to the function. It will return the first character
in the field that is a number.

In the query:

TheNumber: FirstNumber([MyFieldName])

The function:

Public Function FirstNumber(strString As String) As Integer
Dim lngCtr As Long
Dim strTheChar As String

For lngCtr = 1 To Len(strString)
strTheChar = (Mid(strString, lngCtr, 1))
If IsNumeric(strTheChar) Then
FirstNumber = Cint(strTheChar)
Exit For
End If
Next lngCtr
End Function
DB said:
Hi, I have a description field in a table that contains both numbers and
text. I wrote a query with Like "*#*" in the criteria for that field so I
know that all rows returned have at least one number in that field.

Now I would like to return the first number in that field. For example, if
there is a row with AA1A2A, I would like to have 1 returned. If it is
A21AAA, I would like to have 2 returned.

Is this possible?

Thanks in advance for any help.
Click to expand...
Click to expand...
 
Back
Top