Returning only the Numeric portion of a field in a query

[Guest]

Any suggestions as to how I can create a new field in my query that will only
retrieve the numeric part of an existing field?

For instance,

I have a field that is a labled ID and it has both alpha and numeric in the
field, (i.e. 12B34CD), and I want to move only the numeric portion to the new
field. (i.e. 1234, in the example)

Any suggestions?

 

[Ken Snell [MVP]]

Check out the Val function. It will do what you seek:

JustNumbers = Val([ID])

 

[MGFoster]

Check out the Val function. It will do what you seek:

JustNumbers = Val([ID])

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

Unfortunately, that will only return the first numbers in the string.
If the string is "12B34CD" Val() will only return 12. To get the other
numbers you'll have to parse the numbers out of the string. I created a
function to retrieve letters from alphanumeric strings. You can do the
same thing to retrieve numbers: just iterate thru the string, one
character at a time, and save number characters and reject letter
characters.

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

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

iQA/AwUBQlJHjYechKqOuFEgEQIzEwCgjKjXU08LDq/3F+5gtDZ8bLG2nygAn20k
Bacivzsz/ZHpDl45z22MFLRy
=SWd4
-----END PGP SIGNATURE-----

 

[Ken Snell [MVP]]

Thanks, MGF... I misread the post's question.

--

Ken Snell
<MS ACCESS MVP>

Check out the Val function. It will do what you seek:

JustNumbers = Val([ID])

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

Unfortunately, that will only return the first numbers in the string.
If the string is "12B34CD" Val() will only return 12. To get the other
numbers you'll have to parse the numbers out of the string. I created a
function to retrieve letters from alphanumeric strings. You can do the
same thing to retrieve numbers: just iterate thru the string, one
character at a time, and save number characters and reject letter
characters.

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

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

iQA/AwUBQlJHjYechKqOuFEgEQIzEwCgjKjXU08LDq/3F+5gtDZ8bLG2nygAn20k
Bacivzsz/ZHpDl45z22MFLRy
=SWd4
-----END PGP SIGNATURE-----

 

[Ken Snell [MVP]]

Sorry... I misread your post. Here is a function that I use for this
purpose:

' ++++++++++++++++++++++++++++++++++++
' + FUNCTION EXTRACTNUMBERS +
' ++++++++++++++++++++++++++++++++++++

Public Function ExtractNumbers(strFieldValue As String) As String
' *** THIS FUNCTION EXTRACTS THE NUMBERS FROM A TEXT STRING BY
' *** CONCATENATING THE NUMBERS TOGETHER AND IGNORING ANY INTERSPERSED
' *** LETTERS.

Dim intLoop As Integer
Dim strHold As String, strX As String
strHold = ""
For intLoop = 1 To Len(strFieldValue)
strX = Mid(strFieldValue, intLoop, 1)
If IsNumeric(strX) = True Then _
strHold = strHold & strX
Next intLoop
ExtractNumbers = strHold
End Function


--

Ken Snell
<MS ACCESS MVP>

Check out the Val function. It will do what you seek:

JustNumbers = Val([ID])

--

Ken Snell
<MS ACCESS MVP>

Any suggestions as to how I can create a new field in my query that will
only
retrieve the numeric part of an existing field?

For instance,

I have a field that is a labled ID and it has both alpha and numeric in
the
field, (i.e. 12B34CD), and I want to move only the numeric portion to the
new
field. (i.e. 1234, in the example)

Any suggestions?

 

[Guest]

Just writing to thank Ken and others on this thread. I was able to modify
Ken's code to parse out single digit values from a single field and add them
together. The data came from a tax parcel database where the bedrooms of
multiple units on a single property were in a single field and I wanted the
total number of bedrooms on each property. Thanks so much. Brian Kaplan.

' ++++++++++++++++++++++++++++++++++++
' + FUNCTION EXTRACTADDNUMBERS +
' ++++++++++++++++++++++++++++++++++++

Public Function ExtractAddNumbers(strFieldValue As String) As Integer
' *** THIS FUNCTION EXTRACTS THE NUMBERS FROM A TEXT STRING BY
' *** AND ADDS THEM TOGETHER

Dim intLoop As Integer
Dim nSum As Integer, strX As String
nSum = 0
For intLoop = 1 To Len(Trim(strFieldValue))
strX = Mid(strFieldValue, intLoop, 1)
nSum = Val(strX) + nSum
Next intLoop
ExtractAddNumbers = nSum
End Function

Sorry... I misread your post. Here is a function that I use for this
purpose:

' ++++++++++++++++++++++++++++++++++++
' + FUNCTION EXTRACTNUMBERS +
' ++++++++++++++++++++++++++++++++++++

Public Function ExtractNumbers(strFieldValue As String) As String
' *** THIS FUNCTION EXTRACTS THE NUMBERS FROM A TEXT STRING BY
' *** CONCATENATING THE NUMBERS TOGETHER AND IGNORING ANY INTERSPERSED
' *** LETTERS.

Dim intLoop As Integer
Dim strHold As String, strX As String
strHold = ""
For intLoop = 1 To Len(strFieldValue)
strX = Mid(strFieldValue, intLoop, 1)
If IsNumeric(strX) = True Then _
strHold = strHold & strX
Next intLoop
ExtractNumbers = strHold
End Function


--

Ken Snell
<MS ACCESS MVP>

Check out the Val function. It will do what you seek:

JustNumbers = Val([ID])

--

Ken Snell
<MS ACCESS MVP>

Any suggestions as to how I can create a new field in my query that will
only
retrieve the numeric part of an existing field?

For instance,

I have a field that is a labled ID and it has both alpha and numeric in
the
field, (i.e. 12B34CD), and I want to move only the numeric portion to the
new
field. (i.e. 1234, in the example)

Any suggestions?