How to delete characters in a field

A

addressman

I have a text field with data such as R21.004.0500 and would like to create
another field without any of the characters such as 210040500. Any help
would be appreciated, I am a new user to this! Thanks again
 
G

Graham R Seach

Add a reference to Microsoft VBScript Regular Expressions. Then add the
following to a standard module:

Public Enum StripType
se_Char = &H1
se_Num = &H2
se_NonWord = &H4
se_Space = &H8
se_AllButChar = &H10
se_AllButNum = &H20
se_Custom = &H40
End Enum

Public Function StripEx(sText As String, lExpr As StripType, Optional
sUsrExpr As String = "") As String
'-------------------------------------------------------------------------------
' Author: Graham R Seach
' Pacific Database Pty Limited
' Phone: +62 2 9872 9594 Fax: +61 2 9872 9593
' Email: [email protected]
'-------------------------------------------------------------------------------
' Date: 20-06-2004
'
' Function to selectively strip any combination of the following from a
string:
' Characters (a-z or A-Z)
' Numbers (0-9)
' Non-Word characters (~!@#$%, etc. Anything other than a-z, A-Z, 0-9)
' Spaces
'-------------------------------------------------------------------------------
'IMPORTANT: Requires a reference to Microsoft VBScript Regular Expressions
'-------------------------------------------------------------------------------

Dim objRegEx As RegExp
Dim sRegExpr As String

Set objRegEx = New RegExp
If lExpr And se_Custom Then
'User-defined expression
sRegExpr = sUsrExpr
ElseIf lExpr And se_AllButChar Then
'Strip out everything except alpha characters
sRegExpr = "[^a-zA-Z]"
ElseIf lExpr And se_AllButNum Then
'Strip out everything except numbers
sRegExpr = "\D"
Else
'Custom strip
If lExpr And se_Char Then sRegExpr = "[a-zA-Z]"
If lExpr And se_Num Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
0, "|", "") & "\d"
If lExpr And se_NonWord Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
0, "|", "") & "\W"
Click to expand...
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
0, "|", "") & "\s"
End If
'Debug.Print "Regular Expression: " & sRegExpr

With objRegEx
.Pattern = sRegExpr
.Global = True
StripEx = .Replace(sText, "")
End With

Set objRegEx = Nothing
End Function

Then call it using the following syntax:
Me!txtMyTextBox = StripEx(Me!txtMyTextBox, se_AllButNum)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
D

Dirk Goldgar

addressman said:
I have a text field with data such as R21.004.0500 and would like to
create another field without any of the characters such as 210040500.
Any help would be appreciated, I am a new user to this! Thanks again
Click to expand...

Here's an alternative to using regular expressions:

'----- start of code -----
Function StripAllButDigits(varOldNumber As Variant) As String

'Removes any non-numeric characters in a string

Dim i As Integer
Dim intLength As Integer
Dim strThisCharacter As String
Dim strOldNumber As String
Dim strNewNumber As String

strOldNumber = varOldNumber & vbNullString

intLength = Len(strOldNumber)

strNewNumber = vbNullString

For i = 1 To intLength
strThisCharacter = Mid(strOldNumber, i, 1)
If strThisCharacter >= "0" And strThisCharacter <= "9" Then
strNewNumber = strNewNumber & strThisCharacter
End If
Next i

StripAllButDigits = strNewNumber

End Function
'----- end of code -----

You could call it in a calculated control with controlsource

=StripAllButDigits([YourOriginalTextBox])

or in a query:

SELECT SomeField, StripAllButDigits(SomeField)
FROM YourTable;

Or, of course, you could run an update query to permanently the values
stored in a field.
 
Top