The InStr function can find a single character. In the case of a string in a table, illegal characters can cause problems with a query. Or perhaps they just should not be allowed for business rule reasons.
My SQL Server database uses Remote Servers connected to Oracle and other sources. In a import routine, my preference is to test (true/false) to determine if the strings meet nameing standards before importing them.
This also uses an Array and a Loop
This is a short code summary of the function to find a list of illegal characters in a string.
Public Function ContainsIllegalCharactersInString(MyText As String) As Boolean
Dim Illegal As Variant, i As Integer, j As Integer
On Error GoTo err_TRAP
Illegal = Array("\", "/", ":", "*", "?", "#", """", _
"<", ">", "|")
For i = 0 To UBound(Illegal)
j = InStr(1, MyText, Illegal(i))
If j > 0 Then
'MsgBox "illegal character! " & illegal(i) ' for debug or testing
ContainsIllegalCharactersInString = True
ContainsIllegalCharactersInString = False
Debug.Print "Function ContainsIllegalCharactersInString " & Err.Description
To test in a debug window enter the command with the ?:
? ContainsIllegalCharactersInString("My Good String")
? ContainsIllegalCharactersInString("My String Gone Bad?")
Uncomment the Message Box to see specific character