Remove a character from a string 1

[Fubear]

I have a query in one of my forms that should insert a String into a table in an SQL server.

The Query looks like this:
INSERT INTO tablename (KeyF,Text) VALUES (ID,'String')

My problem is that if the string contains a ' character, then access thinks its the end of the string, and throws an error.

Is there any way I can easily pickup on the ' character and either remove it, or put an escape character by it so that it will be correctly entered into the databse.

The string is free text entered by the user, so it is quite likely that someone would enter this charater.

 

[Falcon99]

Apply the Replace function to it before query.

Lookup in help for complete syntax.

Replace("string", "'", "&quot

 

[Fubear]

Perfect - works like a charm.
Thanks a lot

 

[allanon]

If your interested you can also save that character to the SQL database. If you change your line to this:

INSERT INTO tablename (KeyF,Text) VALUES (ID, chr(34) & String & chr(34))

Then you can have a sting with "'" in it.

 

[jwkolker]

Hi all - this is really beating me up - I have many tables have values within fields that have dashes or commas or periods that do not belong - let's use an example - In the table Candidates I have telephone numbers with xxx-xxx-xxxx in field Phone and I want to run a query or create a module to remove the "-"s how do I do it?

Please include full sql for a query or full vba for a module - make this table first to make it easier

Table Name = Candidates
Phone = text - put in a bunch of xxx-xxx-xxxx records and a mistake or two xx-xxx-x-xxxx for example - run your vba or query and update the table or make a table with all xxxxxxxxxx values...

I hope someone can help me figure this one out.

Regards,


John Kolker
Programmer
jwkolker@comcast.net

 

[kphu]

John,

You should create a new post for this, piggy backing on another post will limit the number of readers to your issue.