Handling Apostrophes

[james.igoe]

History:

Took over development of Access project after half of app was
developed. Continued the second half using same coding style as first
developer, but improved upon code and flow, etc. Almost all of the
insertions and deletions were done as DoCMD.RunSQL strSQL.

Our current problem:

All of our prototyping for the business tool was done in with
insertions and deletions, as above, without regard to the problem
introduced by apostrophes.

Rather than rewrite code, I considered using a replace function to
intelligently replace apostrophes with accent marks (underneath tilde).
There are more complex ways of handling this problem, but most require
extensive reworking of existing code.

Any ideas?

[pietlinden]

replace the single apostrophes with double apostrophes and you're done.

[Salad]

ja********@gmail.com wrote:

History:

Took over development of Access project after half of app was
developed. Continued the second half using same coding style as first
developer, but improved upon code and flow, etc. Almost all of the
insertions and deletions were done as DoCMD.RunSQL strSQL.

Our current problem:

All of our prototyping for the business tool was done in with
insertions and deletions, as above, without regard to the problem
introduced by apostrophes.

Rather than rewrite code, I considered using a replace function to
intelligently replace apostrophes with accent marks (underneath tilde).
There are more complex ways of handling this problem, but most require
extensive reworking of existing code.

Any ideas?

Do you have comments in your code? Opening a form and CTRL-Fing for '
and replacing with ~ or some other character database wide would
effectdively make your application unusable.

Are you looking for code to cycle through all modules, forms, and
reports to find something?

Why not CTRL+F for "strSQL = " database wide and fix as required and
then CTRL+F for "Docmd.RunSQL"? By the time you wrote the function you
could be done with it.

[Larry Linson]

<ja********@gmail.com> wrote

Took over development of Access
project after half of app was
developed. . . . Almost all of the
insertions and deletions were done
as DoCMD.RunSQL strSQL.

Almost certainly, by sticking with the choice of unbound forms and writing
SQL manipulate data, you invested more time and energy than you needed to. I
have been in a similar position, but wasn't doing anywhere near _half_ the
application, or I would have made a strong case for revising the half
already done as well as using bound forms in the new work.

Larry Linson
Microsoft Access MVP

[james.igoe]

Multi user app, used by people in Tokyo, Sydney, Singapore, London, and
NY. I would think that the real option would have included using
unbound forms, DAO/ADO, as well as splitting the DB, etc.

[John]

Just happened to be reading about this today. I'm not a devoloper and don't
really know if it applies to your situation, but maybe it might help.

JOhn

Public Function adhHandleQuotes(strValue As String, _
Optional strDelimiter As String = """") As String
' Fix up all instances of a quote within a string by
' breaking up the string, and inserting Chr$(34) whereever
' you find a quote within the string. This way, Jet can
' handle the string for searching.

' From Access 2000 Developer's Handbook, Volume I
' by Getz, Litwin, and Gilbert. (Sybex)
' Copyright 1999. All Rights Reserved.

'
' Solution suggested by Jurgen Welz, a diligent reader.

' In:
' strValue: Value to fix up.
' strDelimiter: (Optional) Delimiter to use.
' Out:
' Return value: the text, with delimiters fixed up.
' Requires:
'
' Example:
' adhHandleQuotes("John ""Big-Boy"" O'Neil") returns
' "John " & Chr$(34) & "Big-Boy" & Chr$(34) & " O'Neil"

Dim strInsert As String
strInsert = "Chr$(" & Asc(strDelimiter) & ")"

adhHandleQuotes = _
strDelimiter & _
Replace(strValue, strDelimiter, _
strDelimiter & " & " & strInsert & " & " & strDelimiter) & _
strDelimiter
End Function
<ja********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

History:

Took over development of Access project after half of app was
developed. Continued the second half using same coding style as first
developer, but improved upon code and flow, etc. Almost all of the
insertions and deletions were done as DoCMD.RunSQL strSQL.

Our current problem:

All of our prototyping for the business tool was done in with
insertions and deletions, as above, without regard to the problem
introduced by apostrophes.

Rather than rewrite code, I considered using a replace function to
intelligently replace apostrophes with accent marks (underneath tilde).
There are more complex ways of handling this problem, but most require
extensive reworking of existing code.

Any ideas?