Text Box And Invalid Characters

jayceesdj

Board Regular
Joined
Nov 29, 2008
Messages
51
I have a Database that has a free form text box with up to 255 characters in it. The problem I am having is that if someone puts in a special character mostly a " ' " then the information will not write to the table.

Is there a way to remove the single quote or any other special characters before I write it to the table.

If I can't remove it is there a way to block the entry of it. If the rep keys in the word "Can't" for example it would kick out an error message.

I have searched the forum and not found the information that will take care of this for me.

I have tried several different things I have seen on this forum and others with no luck.

Any help would be greatly appreciated.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hmmm,
I'm rather curious what others do but I've used this function:

Code:
Public Function StripSingleQuotes(ByVal arg As String) As String
    StripSingleQuotes = Replace(arg, "'", "", 1, -1, vbTextCompare)
    StripSingleQuotes = Replace(StripSingleQuotes, "#", "", 1, -1, vbTextCompare)
End Function

As you see - I'm removing any horrifying single quotes which would ruin my SQL Insert statement. Apparently I dislike # signs as well. I just run this function before inserting:

Code:
Insert Into Table X Values (StripSingleQuotes(myTextBox.Value))

Of course, this could be your after_update on the control, also...
 
Last edited:
Upvote 0
I have a Database that has a free form text box with up to 255 characters in it. The problem I am having is that if someone puts in a special character mostly a " ' " then the information will not write to the table.

Is there a way to remove the single quote or any other special characters before I write it to the table.

If I can't remove it is there a way to block the entry of it. If the rep keys in the word "Can't" for example it would kick out an error message.

I have searched the forum and not found the information that will take care of this for me.

I have tried several different things I have seen on this forum and others with no luck.

Any help would be greatly appreciated.

You could check your field for a-z,A-Z, 0-9 plus any characters you consider valid like
"," comma,
"." period,
"?" question mark,
"-" dash,
"_" underscore .... with a function.

And as xenou says - it could be in your AfterUpdate event.

Function could be based on this:

Code:
 Function fExtractStr(ByVal strInString As String) As String
' From Dev Ashish
'(Q) How do I extract only characters from a string which has 
'both numeric and alphanumeric characters?

'(A) Use the following function. Note that the If loop can be modified 
'to extract either both Lower and Upper case character or either
'Lower or Upper case characters.

'************ Code Start **********

Dim lngLen As Long, strOut As String
Dim i As Long, strTmp As String

    lngLen = Len(strInString)
    strOut = ""
    For i = 1 To lngLen
        strTmp = Left$(strInString, 1)
        strInString = Right$(strInString, lngLen - i)
        'The next statement will extract BOTH Lower and Upper case chars
        If (Asc(strTmp) >= 65 And Asc(strTmp) <= 90) Or _
            (Asc(strTmp) >= 97 And Asc(strTmp) <= 122) Then
            'to extract just lower case, use the limit 97 - 122
            'to extract just upper case, use the limit 65 - 90
'
'********** could add other checks here *********
'
            strOut = strOut & strTmp
        End If
    Next i
    fExtractStr = strOut
End Function
 
Upvote 0
Thank you both for your assistance with this. I was finally able to get it to work by using a variation of this in the on key press feature under the text box features.

I didn't think I would have to list out each character I was willing to accept but I did and it is working.

Thank you both,
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top