Well, I'm a dunce. I just ran into what has to be one of the oldest problems
around, and while I have a couple ways of getting past it, I'm interested in
the best or most efficient way.

I was using Docmd.RunSQL to insert records but forgot that some names have
apostrophes in them.

BatchID being Long, CustomerName being String, and Amount being Currency:

Dim strSQL as String
'fields to insert
strSQL = "INSERT INTO MyTable(BatchID,CustomerName,Amount)"
'insert this data
strSQL = strSQL & " SELECT " & BatchID & ",'" & CustomerName & "'," & Amount
Docmd.RunSQL strSQL

Of course, it worked fine for the Smiths but choked on O'Donnell. (This is
in the middle of a long loop through a recordset, not just a single record.)

Should I:

1. Use a query & pass it the data as parameters?
2. Do a Replace (CustomerName,"'","")

Some more clever idea?

Suggestions:

1. Use the double-quote rather than the single quote.
Double-quotes are quite uncommon in names.
This kind of thing:
strSQL = strSQL & " SELECT " & BatchID & _
", """ & CustomerName & """, " & Amount

If the quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

2. Use Execute with dbFailOnError:
This gives you much better information about the resuts than RunSQL.

Details in:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Brian" <(E-Mail Removed)> wrote in message
news:97C5CD2B-A4FB-476C-8A89-(E-Mail Removed)...
> Well, I'm a dunce. I just ran into what has to be one of the oldest
> problems
> around, and while I have a couple ways of getting past it, I'm interested
> in
> the best or most efficient way.
>
> I was using Docmd.RunSQL to insert records but forgot that some names have
> apostrophes in them.
>
> BatchID being Long, CustomerName being String, and Amount being Currency:
>
> Dim strSQL as String
> 'fields to insert
> strSQL = "INSERT INTO MyTable(BatchID,CustomerName,Amount)"
> 'insert this data
> strSQL = strSQL & " SELECT " & BatchID & ",'" & CustomerName & "'," &
> Amount
> Docmd.RunSQL strSQL
>
> Of course, it worked fine for the Smiths but choked on O'Donnell. (This is
> in the middle of a long loop through a recordset, not just a single
> record.)
>
> Should I:
>
> 1. Use a query & pass it the data as parameters?
> 2. Do a Replace (CustomerName,"'","")
>
> Some more clever idea?

On Sun, 12 Oct 2008 21:10:01 -0700, Brian <(E-Mail Removed)>
wrote:

>Well, I'm a dunce. I just ran into what has to be one of the oldest problems
>around, and while I have a couple ways of getting past it, I'm interested in
>the best or most efficient way.
>
>I was using Docmd.RunSQL to insert records but forgot that some names have
>apostrophes in them.
>
>BatchID being Long, CustomerName being String, and Amount being Currency:
>
>Dim strSQL as String
>'fields to insert
> strSQL = "INSERT INTO MyTable(BatchID,CustomerName,Amount)"
>'insert this data
> strSQL = strSQL & " SELECT " & BatchID & ",'" & CustomerName & "'," & Amount
>Docmd.RunSQL strSQL
>
>Of course, it worked fine for the Smiths but choked on O'Donnell. (This is
>in the middle of a long loop through a recordset, not just a single record.)
>
>Should I:
>
>1. Use a query & pass it the data as parameters?
>2. Do a Replace (CustomerName,"'","")
>
>Some more clever idea?

Third option: delimit with " instead of with '.

strSQL = strSQL & " SELECT " & BatchID & ",""" & CustomerName & """," & Amount

strSQL will resemble

SELECT 123, "O'Donnell", 55.00

and will insert the name with no quibbles.
--

John W. Vinson [MVP]

Thank you, Allen (and Chris & John).

Every day, I learn something new. Both the double-quotes in this context &
the Execute method of the DB using the SQL string are new to me.

"Allen Browne" wrote:

> Suggestions:
>
> 1. Use the double-quote rather than the single quote.
> Double-quotes are quite uncommon in names.
> This kind of thing:
> strSQL = strSQL & " SELECT " & BatchID & _
> ", """ & CustomerName & """, " & Amount
>
> If the quotes don't make sense, see:
> Quotation marks within quotes
> at:
> http://allenbrowne.com/casu-17.html
>
> 2. Use Execute with dbFailOnError:
> This gives you much better information about the resuts than RunSQL.
>
> Details in:
> Action queries: suppressing dialogs, while knowing results
> at:
> http://allenbrowne.com/ser-60.html
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Brian" <(E-Mail Removed)> wrote in message
> news:97C5CD2B-A4FB-476C-8A89-(E-Mail Removed)...
> > Well, I'm a dunce. I just ran into what has to be one of the oldest
> > problems
> > around, and while I have a couple ways of getting past it, I'm interested
> > in
> > the best or most efficient way.
> >
> > I was using Docmd.RunSQL to insert records but forgot that some names have
> > apostrophes in them.
> >
> > BatchID being Long, CustomerName being String, and Amount being Currency:
> >
> > Dim strSQL as String
> > 'fields to insert
> > strSQL = "INSERT INTO MyTable(BatchID,CustomerName,Amount)"
> > 'insert this data
> > strSQL = strSQL & " SELECT " & BatchID & ",'" & CustomerName & "'," &
> > Amount
> > Docmd.RunSQL strSQL
> >
> > Of course, it worked fine for the Smiths but choked on O'Donnell. (This is
> > in the middle of a long loop through a recordset, not just a single
> > record.)
> >
> > Should I:
> >
> > 1. Use a query & pass it the data as parameters?
> > 2. Do a Replace (CustomerName,"'","")
> >
> > Some more clever idea?
>
>