Extracting num values from character fields

[Guest]

How can I extract only numeric values from a character field? For instance: Field value "ABC-056#Y89" - I would want to see "05689" only, in a query. Can I do this in Access alone or do I have to find someone to write a Visual Basic program for this?

 

[Van T. Dinh]

You can use code to inspect each character in the Field and if the character
is between "0" and "9", append it to a strNum.

At the end, you should have the strNum = "05689" which can be converted to a
numerical value using Val() or CInt() or CLng().

(slightly more complex if you need decimal numbers).

--
HTH
Van T. Dinh
MVP (Access)

How can I extract only numeric values from a character field? For

instance: Field value "ABC-056#Y89" - I would want to see "05689" only, in a
query. Can I do this in Access alone or do I have to find someone to write
a Visual Basic program for this?

 

[Guest]

Can I purchase this code from you or someone else or is there a utiliy available I can purchase? How would I get the code embedded into my database?

----- Van T. Dinh wrote: -----

You can use code to inspect each character in the Field and if the character
is between "0" and "9", append it to a strNum.

At the end, you should have the strNum = "05689" which can be converted to a
numerical value using Val() or CInt() or CLng().

(slightly more complex if you need decimal numbers).

--
HTH
Van T. Dinh
MVP (Access)

How can I extract only numeric values from a character field? For

instance: Field value "ABC-056#Y89" - I would want to see "05689" only, in a
query. Can I do this in Access alone or do I have to find someone to write
a Visual Basic program for this?

 

[Van T. Dinh]

Tested in A2K:

****
Public Function fnGetDigitsOnly(ByVal varInput As Variant) As String
Dim strInput As String
Dim strTemp As String
Dim strChar As String
Dim intIndex As Integer

strTemp = ""
If (IsNull(varInput) = False) Then
strInput = varInput
For intIndex = 1 To Len(Trim(strInput))
strChar = Mid$(strInput, intIndex, 1)
If (strChar >= "0") And (strChar <= "9") Then
strTemp = strTemp & strChar
End If
Next intIndex
End If

fnGetDigitsOnly = strTemp
End Function
****

Usage:

?fnGetDigitsOnly("abc123efg456&*(7890&hghg jjh")
1234567890

--
HTH
Van T. Dinh
MVP (Access)

Can I purchase this code from you or someone else or is there a utiliy

available I can purchase? How would I get the code embedded into my
database?