Solved

VBA Replace Values

Posted on 2007-11-21
4
1,394 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:andyb7901
  • 2
4 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 20327666

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

Expert Comment

by:Ashish Patel
ID: 20327722
Replace ' single quote with 2 single quote and all would be fine.
 Me("strMessage").Value = Replace(strMessage, "'", "''")
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 20327745
what value do you want as replacement?

0
 
LVL 18

Accepted Solution

by:
Sham Haque earned 500 total points
ID: 20328169
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now