Link to home
Start Free TrialLog in
Avatar of wlwebb
wlwebbFlag for United States of America

asked on

Access VB Function to strip Special Characters

Hello All

Have a function that thought would strip all Special Characters from a String.  If I step through I am getting Error on all the unbound fields using the function.

Public Function fStripIllegal(strCheck As String, Optional strReplaceWith As String = "") As String

    On Error GoTo StripIllErr
    'illegal file name characters included in default string are    ? [ ] /  = + < > :; * " , '

    Dim intI As Integer
    Dim intPassedString As Integer
    Dim intCheckString As Integer
    Dim strChar As String
    Dim strIllegalChars As String
    Dim intReplaceLen As Integer

    If IsNull(strCheck) Then Exit Function

    strIllegalChars = "?[]-_/=+<>:;,*" & Chr(34) & Chr(39)  'add/remove characters you need removed to this string

    intPassedString = Len(strCheck)
    intCheckString = Len(strIllegalChars)

    intReplaceLen = Len(strReplaceWith)

    If intReplaceLen > 0 Then   'a character has been entered to use as the replacement character
    
        If intReplaceLen = 1 Then   'check the character itself isn't an illegal character
        
            If InStr(strIllegalChars, strReplaceWith) > 0 Then
                MsgBox "You can't replace an illegal character with another illegal character", _
                       vbOKOnly + vbExclamation, "Invalid Character"
                fStripIllegal = strCheck
                Exit Function
            End If

        Else   'only one replacement character allowed

            MsgBox "Only one character is allowed as a replacement character", _
                   vbOKOnly + vbExclamation, "Invalid Replacement String"
            fStripIllegal = strCheck
            Exit Function
            
        End If
    End If

    If intPassedString < intCheckString Then

        For intI = 1 To intCheckString
            strChar = Mid(strIllegalChars, intI, 1)
            If InStr(strCheck, strChar) > 0 Then
                strCheck = Replace(strCheck, strChar, strReplaceWith)
            End If
        Next intI

    Else

        For intI = 1 To intPassedString
            strChar = Mid(strIllegalChars, intI, 1)
            If InStr(strCheck, strChar) > 0 Then
                strCheck = Replace(strCheck, strChar, strReplaceWith)
            End If
        Next intI

    End If

    fStripIllegal = Trim(strCheck)

StripIllErrExit:
    Exit Function

StripIllErr:
    MsgBox "The following error occured: " & Err.Number & vbCrLf _
         & Err.Description, vbOKOnly + vbExclamation, "Unexpected Error"

    fStripIllegal = strCheck

    Resume StripIllErrExit

End Function

Open in new window

Tried getting around the Error with this:
=IIf(IsError(IIf(nz([EmpFirstName],"")="","",fStripIllegal([EmpFirstName])))=True,[EmpFirstName],IIf(nz([EmpFirstName],"")="","",fStripIllegal([EmpFirstName])))

Open in new window


That didn't work.

Anyone see where the Function is incorrect?  What if there are no Special Characters...or is there a better function for Access 2007 to strip any Special Characters.  (Note for this purpose the fields that the unbound textboxes are getting the original data from can in-fact allow special characters but I need the String without them for something else.
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

If your goal is to simply remove those characters, just use the Replace function:

Dim s As String
s = "Your String Here"
s = Replace(s, "[", "")
s = Replace(s, "]", "")
etc etc
What about other non alpha and numeric characters, like ^ ( )! {}

Might be easier to create a String of valid characters
0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ
Or, (if applicable), simply prevent invalid characters from ever being entered in the first place:

Private Sub YourControl_KeyPress(KeyAscii As Integer)
    Select Case KeyAscii
        Case Asc("!")
            KeyAscii = 0
        Case Asc("[")
            KeyAscii = 0
        Case Asc("]")
            KeyAscii = 0
        'Continue with all other illegal characters
    End Select
End Sub


;-)

JeffCoachman
Avatar of wlwebb

ASKER

Boag
Though in general I agree don't allow them in the first place but in this problem I acknowledged the orig source data string was in fact allowed to have them but I needed the special character free string for another purpose.  Hence the question
Yes, I understood that your question was: "I have characters that I need to remove."

I just made my post as just some extra info for future reference, if you ever needed it.

;-)

Jeff
Avatar of wlwebb

ASKER

Thanks Jeff for the input.... still leaving open to see if there is any other solution
I tried your function out, and when the string is in a table, it works fine, unless the string is empty (null).  All illegal characters, no illegal characters, mix of same.
Can you post a database with necessary table(s), form(s), etc. Table(s) can be empty.
ASKER CERTIFIED SOLUTION
Avatar of jerryb30
jerryb30
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wlwebb

ASKER

Thanks all