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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
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:

jackd

Well-known Member
Joined
Oct 19, 2006
Messages
1,311
Office Version
  1. 365
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
 

jayceesdj

Board Regular
Joined
Nov 29, 2008
Messages
51
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,
 

Watch MrExcel Video

Forum statistics

Threads
1,127,873
Messages
5,627,399
Members
416,245
Latest member
Xterminat

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
Top