Escaping Special Characters in Access

[kbelkus]

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

 

[KenHigg]

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. ???

 

[CyberLynx]

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.

.

 

[kbelkus]

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

 

[KenHigg]

You're welcome but Cyberlynx did the sample code

 

[DCrake]

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.