How do I take specific characters from a string

[vbreezy]

I have a field that is for a personal id with a length of three alphanumeric. I need to clean this field up to include ONLY numbers or letters. There should be no dashes or pound signs or spaces in these fields. I can trim off the spaces, but does anyone know how I can remove the unwanted characters?

Here are some examples:
-1
-1-
35
#5
35-
1
--1


They should be listed like this:
1
1
35
5
35
1
1

 

[tlbroadbent]

Create a VBA function to utilize the VBA function, REPLACE.

Function QryReplace(sStr As String) As String
Dim sNew As String
sNew = Replace(sStr, "-", "", 1, -1, vbTextCompare)
sNew = Replace(sNew, "#", "", 1, -1, vbTextCompare)
sNew = Replace(sNew, ".", "", 1, -1, vbTextCompare)
sNew = Replace(sNew, " ", "", 1, -1, vbTextCompare)
QryReplace = sNew
End Function

Call the new function form an Update query as follows.

UPDATE tbl SET PersonalID=QryReplace(PersonalID)
WHERE PersonalID Like "*-*"
OR PersonalID Like "*.*"
OR PersonalID Like "*#*"
OR PersonalID Like "* *" Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[vbreezy]

How do I reference the VBA Function, REPLACE, in Access 97?

 

[tlbroadbent]

Unfortunately, replace is not available in Access 97. Here are some links to solutions written for Access 97.

http://www.mvps.org/access/strings/str0004.htm

http://p2p.wrox.com/archive/access/2001-04/8.asp

http://www.aldex.co.uk/codesamp.html

Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[PaulF]

try this

Public Function ParseIt(strToBeParsed As String)
' This function will parse any character except alpha characters
' and numeric characters from a string, including any spaces
Dim lngLength As Long, i As Long, ParsedString As String
i = 1
lngLength = Len(strToBeParsed)
For i = 1 To lngLength
Select Case Asc(Mid(strToBeParsed, i, 1))
Case 48 To 57
ParsedString = IIf(IsNull(ParsedString), Mid(strToBeParsed, i, 1), ParsedString & Mid(strToBeParsed, i, 1))
Case 65 To 90
ParsedString = IIf(IsNull(ParsedString), Mid(strToBeParsed, i, 1), ParsedString & Mid(strToBeParsed, i, 1))
Case 97 To 122
ParsedString = IIf(IsNull(ParsedString), Mid(strToBeParsed, i, 1), ParsedString & Mid(strToBeParsed, i, 1))
Case Else
ParsedString = IIf(IsNull(ParsedString), "", ParsedString)
End Select
Next i
ParseIt = ParsedString
End Function

PaulF