Foolproof Query on Quote and Apostrophe Text?

[David Kaye]

I always back away from these stupid delimiter problems by replacing quotes
and apostrophes with upper-ascii characters that look like them (chr$(146)
and chr$(148), for example). But I'm sick of the CPU drag that all the
replacing causes.

So, I'm looking for THE definitive query delimiter sequence I can use with a
Jet database that will allow me to manipulate text fields regardless of
their contents.

These are examples of the titles in a music database:

Fool on the Hill
Scherzo from "A Midsummer Night's Dream"
Bob's Breakdown
"Revolution #9"
O'Brien's Song

Note that there will be combinations of quotation marks and apostrophes. I
have never found a way to handle all of the above.

Help! Anyone?

 

[John Spencer]

I would just store the straight quotes and apostrophes.

The only time I can think of that you would have a problem is when you are
using code and a literal string.

So in that case you could use a public vba function to handle the situation.
Something like the following untested function.

Public Function fHandleStringQuotes(vStringIn As Variant) As Variant
Const X As String = """"

If Len(vStringIn & vbNullString) = 0 Then
fHandleStringQuotes = vStringIn
Else
fHandleStringQuotes = X & Replace(vStringIn, X, X & X) & X
End If
End Function


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County