ACCESS FEED: Re: [MS_AccessPros] Remove Special charactors from string.

Minggu, 10 Maret 2013

Re: [MS_AccessPros] Remove Special charactors from string.

hi Michael,

You ought to be able to get what you need some these examples.

Private Sub cmdTestNonAlphanumeric_Click()
Dim strPassString As String
Dim strResults As String
'Stop
'strPassString = ActiveSheet.Range("StringToPass").Value
strPassString = TestSheet.Range("StringToPass").Value

strResults = AlphaNumericOnly(strPassString)

Debug.Print strResults
'Stop
strResults = IsAlpha(strPassString)

Debug.Print strResults

End Sub

Function IsAlpha( ByVal stString As String) As Boolean

IsAlpha = Not (stString Like "*[!a-z,A-Z,0-9]*")

End Function

Public Function AlphaNumericOnly(strIn As String) As Boolean
Dim x As Integer
Dim varCharacter As Variant
Dim bolANOnly As Boolean

'Initialize flag to True.
bolANOnly = True

If IsNull(strIn) Or strIn = "" Then Exit Function

'Loop through entire string.
For x = 1 To Len(strIn)
GoTo AlternateCase
'Get a character in the string.
varCharacter = Mid(strIn, x, 1)
Select Case Asc(varCharacter)
'Alpha numeric characters.
Case 48 To 57, 65 To 90, 97 To 122
bolANOnly = True
Case Else
bolANOnly = False
Exit For
End Select

AlternateCase:
'Get a character in the string.
varCharacter = Mid(strIn, x, 1)
Select Case varCharacter
Case "A" To "Z", "a" To "z", 0 To 9 'Alpha numeric characters.
bolANOnly = True
Case Else
bolANOnly = False
Exit For
End Select
Next

AlphaNumericOnly = bolANOnly

End Function

Jeff

----- Original Message -----

Naming and moving files, system creates file names as combination of several fields one is a text field.
Would like to step through the text field and remove anything that is not alpha numeric
ie, a-z, A-Z, and 0-9

anyone have one done?

Take care,
Michael

[Non-text portions of this message have been removed]

--

Jeffrey Park Jones
Excel, Access, Word, Office Expert
Excel and Access, LLC®
http://ExcelAndAccess.Com

919-671-9870
5109 Deer Lake Trail
Wake Forest, NC 27587
jpjones23@centurylink.net

[Non-text portions of this message have been removed]