remove alpha content in text field

  • Thread starter Thread starter vw
  • Start date Start date
V

vw

Is there an existing command (ie an easy way) to remove
alpha entries in an existing alpha-numeric field. EG I
want to see results where original field entry is AB1234,
result field is 1234.
Any ideas?
 
Is there an existing command (ie an easy way) to remove
alpha entries in an existing alpha-numeric field. EG I
want to see results where original field entry is AB1234,
result field is 1234.
Any ideas?
Click to expand...

If the number was always at the beginning of the field, i.e. 1234AB,
you could use in a query:
Resultfield:Val([FieldName])

But as long as the numbers can be anywhere within the field you'll
need to check each character.
If the numbers in the field are going to be all together (AB1234) as
in your sample data, and not mixed up within the field (ab4cv5k6) the
following will work for you.

You need to create a user defined function to cycle through the value
in each record until you find the first number character:

In a Module:

Function ValNumber(strString As String) As Integer
On Error GoTo Err_Handler

Dim intX As Integer
Dim intY As Integer
intY = Asc(strString)
Do While intY < 48 Or intY > 57
intX = intX + 1
If intX = Len(strString) Then Exit Do
intY = Asc(Mid(strString, intX, 1))
Loop
If intX = 0 Then intX = 1

ValNumber = Val(Mid(strString, intX))

Exit_ValNumber:
Exit Function
Err_Handler:
Resume Exit_ValNumber

End Function
===
You can call it from a query:
Result:ValNumber([FieldName])

As criteria in the query use:
Where [FieldName] is not null

If the numbers are mixed up within the field, i.e. ab4kl6fv9, the
method would be similar to the above, but you would have to cycle
through each character in the string and move the numbers to a
different string.
If that's the case, and you can't figure out how, post back and I'll
send the code along.
 
I don't know if you want to go to this much trouble, and there's no
guarantee that the Administrators have allowed VBScript RegEx on the
computers, but if they have, this might just do the trick:

Add a reference to Microsoft VBScript Regular Expressions 5.5 (or the latest
verion you have). 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
'---------------------------------------------------------------------------
----
' 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

You can then use it in VBA, or more importantly in this case - a query, by
replacing the enum with its decimal value. For example, to strip out all
characters, use the following syntax:
SELECT StripEx("abc123", 1) FROM MSysObjects

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Thks for this. Alpha is at beginning of field so what
you've sent should suffice.
Regds
-----Original Message-----
Is there an existing command (ie an easy way) to remove
alpha entries in an existing alpha-numeric field. EG I
want to see results where original field entry is AB1234,
result field is 1234.
Any ideas?
Click to expand...

If the number was always at the beginning of the field, i.e. 1234AB,
you could use in a query:
Resultfield:Val([FieldName])

But as long as the numbers can be anywhere within the field you'll
need to check each character.
If the numbers in the field are going to be all together (AB1234) as
in your sample data, and not mixed up within the field (ab4cv5k6) the
following will work for you.

You need to create a user defined function to cycle through the value
in each record until you find the first number character:

In a Module:

Function ValNumber(strString As String) As Integer
On Error GoTo Err_Handler

Dim intX As Integer
Dim intY As Integer
intY = Asc(strString)
Do While intY < 48 Or intY > 57
intX = intX + 1
If intX = Len(strString) Then Exit Do
intY = Asc(Mid(strString, intX, 1))
Loop
If intX = 0 Then intX = 1

ValNumber = Val(Mid(strString, intX))

Exit_ValNumber:
Exit Function
Err_Handler:
Resume Exit_ValNumber

End Function
===
You can call it from a query:
Result:ValNumber([FieldName])

As criteria in the query use:
Where [FieldName] is not null

If the numbers are mixed up within the field, i.e. ab4kl6fv9, the
method would be similar to the above, but you would have to cycle
through each character in the string and move the numbers to a
different string.
If that's the case, and you can't figure out how, post back and I'll
send the code along.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...
 
Back
Top