Quicker way to process data with single quotes during INSERT

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I have several tables with quite a few fields and I'm getting errors when
trying to insert records with single quotes in the data like:
name = John O'Henry or a city name of O'Fallen

So I went ahead and added a replace to replace the ' with " but now other
fields are having the same problem and there are multiple fields/tables
involved. This data gets into having a lot of symbols used, etc.

So rather than go through and replace these quotes in each and every field
in each table is there a quick or automated way to do this in access/vb with
maybe a single line of code or perhaps a quick way to add it in the
querybuilder?

The code in VB is so clean right now and adding all these replaces and
changing variable names will take forever and make a mess of the code. Any
suggestions?
 
Use a function like this (put it in a regular module):

Public Function SingleQDouble(ByVal xstrReplaceStringValue) As String
'***THIS FUNCTION CONVERTS ONE SINGLE-QUOTE CHARACTER INTO TWO SINGLE-QUOTE
'***CHARACTERS IN A TEXT STRING.

' xstrReplaceStringValue is string variable that contains the text string
that
' needs to be converted
On Error Resume Next
SingleQDouble = Replace(xstrReplaceStringValue, "'", "''", 1, -1,
vbTextCompare)
Err.Clear
End Function


Then use the function in the query to wrap all the text fields.
 
Then use the function in the query to wrap all the text fields.
Click to expand...


Forgive me, but what do you mean by use the function to wrap all the text
fields?


something like:

INSERT INTO (field1,field2) VALUES (function(value1), function(value2))

???
 
Yes.

--

Ken Snell
<MS ACCESS MVP>

Jason said:
Forgive me, but what do you mean by use the function to wrap all the text
fields?


something like:

INSERT INTO (field1,field2) VALUES (function(value1), function(value2))

???
Click to expand...
 
Back
Top