Hey,

I hope everyone is well.

I am trying to solve a minor problem with my Database. I am trying to 'escape' characters.
My users can insert information into a textbox. This data is then stored as a variable and then used within my INSERT SQL statement.

The problem is, VBA script spits out an error: ("Syntax Error (Missing Operator) in Query Expression"). Whenever my users use a set of special characters: ',",[,],#.

How do I go about repairing this problem. It's important that my users can enter these characters if they need to.

Any help or clues would be appreciated.

Kindest Regards
Kieran

One work around may be to instruct your users to put double characters in place of these. For example use "##" instead of "#". Or you may be able to do a function to scan the text string and do the conversion for you. ???

As Ken said, you could do a function to remove instances of these unwanted characters. I quickly whipped a simple one up for you. The Function is named ReplaceEx. Just copy and past the following into a Database Code Module:


Public Function ReplaceEx(ByVal SourceStrg As String, ByVal SearchStrg As String, _
Optional ByVal ReplaceStrg As Variant = "") As Variant
Dim Multi As Integer

SearchStrg = Replace(SearchStrg, ",,,", ",~|~,"): SearchStrg = Replace(SearchStrg, ",,", ",~|~")
If (InStr(SearchStrg, ",") > 0) Or (InStr(SearchStrg, "~|~") > 0) Then
Dim SearchStrgArray() As String
SearchStrgArray = Split(SearchStrg, ",")
Multi = 1
End If

ReplaceStrg = Nz(ReplaceStrg, "")
ReplaceEx = SourceStrg

If Multi = 1 Then
For Multi = LBound(SearchStrgArray) To UBound(SearchStrgArray)
If SearchStrgArray(Multi) = "~|~" Then SearchStrgArray(Multi) = ","
ReplaceEx = Replace(ReplaceEx, SearchStrgArray(Multi), ReplaceStrg)
Next Multi
Else
ReplaceEx = Replace(SourceStrg, SearchStrg, ReplaceStrg)
End If

If ReplaceEx = "," Then ReplaceEx = ""
If Len(ReplaceEx) = 0 Then ReplaceEx = Null

Erase SearchStrgArray
End Function


Usage:

ReplaceEx(String,SubString,NewSubString)

Examples:

(1) MsgBox ReplaceEx("This is Tony's desk.", "'") OR MsgBox ReplaceEx("This is Tony's desk.", "'", "")

Returns: This is Tonys desk.

(2) MsgBox ReplaceEx("This [is] *Tony's* desk.", "[,],*,'","")

Returns: This is Tonys desk.

Notice the unwanted characters are delimited by a comma i the above example. What if you don't want commas? The delimit a comma within a comma:

(3) MsgBox ReplaceEx("This [is] *Tony's* desk, I think.", "[,],*,,,'","")

Returns: This is Tonys desk I think.

(4) MsgBox ReplaceEx("Who is Tony for heavens sake?", "Tony", "Frank")

Returns: Who is Frank for heavens sake?

The NewSubString argument is optional. If nothing is supplied then when a SubString is detected it is turn into nothing.

If ReplaceEx contains a zero length string ("") then the Function Returns NULL.

.

KenHigg + Cyberlynx. Thank you for your replies. KenHigg: Thanks for going to the trouble to wip up an example, it's appreciated.

You're welcome but Cyberlynx did the sample code :p

Another example is to use the Keypress event and examine the keycode for specific characters and if encountered use sendkeys {BackSpace} to erase the last character typed in.