Rx_
Nothing In Moderation
- Local time
- Today, 07:44
- Joined
- Oct 22, 2009
- Messages
- 2,803
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.
Keywords: 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.
To test in a debug window enter the command with the ?:
? ContainsIllegalCharactersInString("My Good String")
False
? ContainsIllegalCharactersInString("My String Gone Bad?")
True
Uncomment the Message Box to see specific character
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.
Keywords: 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.
Code:
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
Exit Function
Exit For
End If
Next
ContainsIllegalCharactersInString = False
Exit Function
err_TRAP:
Debug.Print "Function ContainsIllegalCharactersInString " & Err.Description
End Function
To test in a debug window enter the command with the ?:
? ContainsIllegalCharactersInString("My Good String")
False
? ContainsIllegalCharactersInString("My String Gone Bad?")
True
Uncomment the Message Box to see specific character