remove letters from an alpha-numeric field

[rciolkosz]

I have 6 digit asset numbers in a field. Problem is that there are all sorts
of alpha attached to the asset numbers. Example: AT-604781, SPARE-207841.
All I want is the 6 digit asset. I would also like to fix this in an
expression field.

 

[Jeff Boyce]

One approach might be to look for the "-" and take everything to the right.

Another might be to take only the right-most 6 characters.

Can you guarantee that ALL asset numbers are stored as:

AA...AA-######

(look at creating a query and using the Right() function and/or the Mid() &
Instr() functions)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dirk Goldgar]

I have 6 digit asset numbers in a field. Problem is that there are all
sorts
of alpha attached to the asset numbers. Example: AT-604781,
SPARE-207841.
All I want is the 6 digit asset. I would also like to fix this in an
expression field.

The best way to approach this depends on how regular the values are. If the
asset numbers are always present, always 6 digits, and always at the end of
the value, then you can just get the rightmost 6 characters:

=Right([YourAssetNumberField], 6)

If the asset numbers might not always be 6 digits, but will always be the
last thing following a hyphen, then you could do this:

=Mid([YourAssetNumberField], InStrRev([YourAssetNumberField], "-") + 1)

If you can't count on the hyphen being present, but you always want the last
string of numeric digits, however long, then you could use an expression
like this:

=Mid([YourAssetNumberField], fncLastNonNumeric([YourAssetNumberField]) +
1)

where the function fncLastNonNumeric is defined as follows:

'----- start of air code -----
Function fncLastNonNumeric (pValue As Variant) As Long

' Returns the position of the last non-numeric character in
' a string variant. If the argument is null, or there is no
' non-numeric character, returns 0.

Dim I As Integer
Dim strValue As String

If IsNull(pValue) Then
fncLastNonNumeric = 0
Else

strValue = pValue

For I = Len(strValue) to 1 Step -1
Select Case Asc(Mid(strValue, I, 1))
Case 48 To 57
' It's numeric, so do nothing
Case Else
fncLastNonNumeric = I
Exit Function
End Select
Next I

fncLastNonNumeric = 0

End If

End Function

'----- end of air code -----

That's untested air code, but something along those lines ought to work.

 

[Ken Snell MVP]

Put this function in a regular module, and use it in your query's expression
for that field:

'********************************
'* *
'* Fxn StripAllNonNumericChars *
'* *
'********************************

' ** This function strips all nonnumeric characters from a text string.

Function StripAllNonNumericChars(varOriginalString As Variant) As String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
Dim strOriginalString As String
On Error Resume Next
strTemp = ""
strOriginalString = Nz(varOriginalString, "")
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
For intLoop = Asc("0") To Asc("9")
If strChar = Chr(intLoop) Then
blnStrip = False
Exit For
End If
Next intLoop
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonNumericChars = strTemp
Exit Function
End Function