Replacing text and leaving numbers

[Guest]

RE: Access 2000

I have a text field that has a combination of text and numbers. For
example, in one field there might be "ex 500.00" or "el 650 x"
I want to remove the actual apha characters (and spaces) and simply leave
the numbers. [note - the combinations might be different every time]

Would this be a update query using replace? If so, can someone give me an
example of how to do this?

Thanks in advance for any assistance!!

 

[fredg]

RE: Access 2000

I have a text field that has a combination of text and numbers. For
example, in one field there might be "ex 500.00" or "el 650 x"
I want to remove the actual apha characters (and spaces) and simply leave
the numbers. [note - the combinations might be different every time]

Would this be a update query using replace? If so, can someone give me an
example of how to do this?

Thanks in advance for any assistance!!

Paste the following into a Module:

Public Function RemoveAlpha(StrIn)
' Remove all Alpha characters from a string

If IsNull(StrIn) Then
Exit Function
End If

Dim intX As Integer
Dim intY As Integer
Dim StrOut As String
For intX = 1 To Len(StrIn)
intY = Asc(Mid(StrIn, intX, 1))
If intY >= 48 And intY <= 57 Then
StrOut = StrOut & Chr(intY)
End If
Next intX
RemoveAlpha = StrOut
End Function
====================

Then you can call it from a query:
NumOnly:RemoveAlpha([FieldName])

 

[John Spencer]

Note that Fred's routine works well, but it will return 50000 for 500.00 (per
your first example).

If that is ok, then forget my comment. If not and you do not know how to modify
the routine, post back so he or I or someone else can suggest how to change the routine.

Also, do you ever have more than one number series in the field? And if so,
what do you want returned?

RE: Access 2000

I have a text field that has a combination of text and numbers. For
example, in one field there might be "ex 500.00" or "el 650 x"
I want to remove the actual apha characters (and spaces) and simply leave
the numbers. [note - the combinations might be different every time]

Would this be a update query using replace? If so, can someone give me an
example of how to do this?

Thanks in advance for any assistance!!

Paste the following into a Module:

Public Function RemoveAlpha(StrIn)
' Remove all Alpha characters from a string

If IsNull(StrIn) Then
Exit Function
End If

Dim intX As Integer
Dim intY As Integer
Dim StrOut As String
For intX = 1 To Len(StrIn)
intY = Asc(Mid(StrIn, intX, 1))
If intY >= 48 And intY <= 57 Then
StrOut = StrOut & Chr(intY)
End If
Next intX
RemoveAlpha = StrOut
End Function
====================

Then you can call it from a query:
NumOnly:RemoveAlpha([FieldName])

 

[Guest]

I should have prefaced everything by giving you my skill level. I have never
used modules before. However, I will take this information you gave me and
see what I can do.

Thanks - Have a nice day.

Note that Fred's routine works well, but it will return 50000 for 500.00 (per
your first example).

If that is ok, then forget my comment. If not and you do not know how to modify
the routine, post back so he or I or someone else can suggest how to change the routine.

Also, do you ever have more than one number series in the field? And if so,
what do you want returned?

RE: Access 2000

I have a text field that has a combination of text and numbers. For
example, in one field there might be "ex 500.00" or "el 650 x"
I want to remove the actual apha characters (and spaces) and simply leave
the numbers. [note - the combinations might be different every time]

Would this be a update query using replace? If so, can someone give me an
example of how to do this?

Thanks in advance for any assistance!!

Paste the following into a Module:

Public Function RemoveAlpha(StrIn)
' Remove all Alpha characters from a string

If IsNull(StrIn) Then
Exit Function
End If

Dim intX As Integer
Dim intY As Integer
Dim StrOut As String
For intX = 1 To Len(StrIn)
intY = Asc(Mid(StrIn, intX, 1))
If intY >= 48 And intY <= 57 Then
StrOut = StrOut & Chr(intY)
End If
Next intX
RemoveAlpha = StrOut
End Function
====================

Then you can call it from a query:
NumOnly:RemoveAlpha([FieldName])