VBA Replace Values

Hi,
Can someone tell me how I would replace a ' in my text field. If I try to update the field it screws up my query! The code I thought I mite use would be something like; (strMessage being my text field name)

         If InStr(strMessage, "'") = 1 Then
                Me("strMessage").Value = Replace(strMessage, "'", " ")
         End If

Woudl this work?
andyb7901Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Sham HaqueConnect With a Mentor Senior SAP CRM ConsultantCommented:
try this:

changes to above are:

Instr ... > 0 - rather than =1. this is the position of the erroneous char you're looking for - assuming that it's not always the first character - that would be simpler to deal with(!)

Also, using Chr(39) rather than escaping a [']

dim strMessage as String
strMessage = me.MyField.Value
 
If InStr(strMessage), Chr(39)) > 0 Then
               strMessage = Replace(strMessage, chr(39), " ")
End If

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:

If InStr(me("strMessage"), "'") =1 Then
                Me("strMessage").Value = Replace(Me("strMessage"), "'", " ")
        End If
0
 
Ashish PatelCommented:
Replace ' single quote with 2 single quote and all would be fine.
 Me("strMessage").Value = Replace(strMessage, "'", "''")
0
 
Rey Obrero (Capricorn1)Commented:
what value do you want as replacement?

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.