Update qry removing ONLY Alpha

[JCarter]

I have a table that's values include both alpha and numeric characters. I
want only the numeric values. How would I build the Update qry to return
this to me. Also, the locations of the numeric charaters vary in each record
of the field, so "Left" or "Right" won't work for me.

Thank you in advance!!!!

 

[Jeff Boyce]

An update query would modify the values in the table. Do you want to
(permanently) change the table values, or are you looking for a way to "see"
only the numeric values?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Michel Walsh]

Maybe not the best way to do it, but something like:


REPLACE( .... REPLACE(REPLACE( UCASE(yourString), "A", "" ), "B", "" ) ....
, "Z", "" )



Vanderghast, Access MVP

 

[John Spencer]

This would require a custom function. Past the following function into a
vba and save the module (modNumONly)

Now you can use the function in an update query if you want to change the
data or in a select query if you want to keep the data as it is currently
stored, but see just the numbers

Field: fStripToNumbersOnly([YourField]

In an update query, the SQL might look like

UPDATE [YourTable]
SET [YourField] = fStripToNumbersOnly([YourField])



Public Function fStripToNumbersOnly(ByVal varText As Variant)

'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As String
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = varText

Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

if Len(strOut) = 0 then
fStripToNumbersOnly = Null
Else
fStripToNumbersOnly = strOut
end if

End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Klatuu]

Here is a function that accepts a string and returns a string containing only
digits 0 -9. All other characters are dropped. If you are going to be using
this as the value to update to, put it in the Update To row something like
this:

=NumbersOnly([FieldToConvert])

Public Function NumbersOnly(strOriginalString As String) As String
Dim lngCtr As Long
Dim lngLength As Long
Dim strTheCharacter As String
Dim intAscii As Integer
Dim strFixed As String

lngLength = Len(strOriginalString)
For lngCtr = 1 To lngLength
strTheCharacter = Mid(strOriginalString, lngCtr, 1)
intAscii = Asc(strTheCharacter)
If intAscii >= 48 And intAscii <= 57 Then
strFixed = strFixed & strTheCharacter
End If
Next lngCtr
NumbersOnly = strFixed

End Function