?
Solved

replace function in vba

Posted on 2014-08-14
3
Medium Priority
?
279 Views
Last Modified: 2014-08-19
I would like to remove instances of single quote ' from a string

ex:
C1 FAMILY'S FRIEND
ASTRAVAN PETROL LS 'LPG'/1.6i Auto

CEE'D DIESEL HATCHBACK 4 1.6 CRDi 5dr

so multiple instances might occur I tried using find replace on table but it put double quote marks in.
0
Comment
Question by:PeterBaileyUk
3 Comments
 
LVL 11

Accepted Solution

by:
Dany Balian earned 1000 total points
ID: 40260179
your question is not very clear, what do you want to do precisely?

do you want to update all your tables so that quotes are removed?
if yes, then use this:

update yourtablename set yourfieldname=replace(yourfieldname,"'","")

Open in new window


if you're inserting new rows using sql, then use:
insert into yourtablename(field1, field2) values(1, 'mc''donalds') 

Open in new window

<<notice the double single quotations

if you're reading rows, then don't worry about the values because access will handle escaping the single quotes.
0
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 1000 total points
ID: 40260182
Using this should replace a single quote with a zero length string

Replace(Sn, "'", "", 1)

In my example you need to put something else in the place of Sn, and this is where VBA will look for the quotes - it could be a string variable.

Kelvin
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 40269646
sorry for delay..holidays
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

864 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