469,950 Members | 1,903 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,950 developers. It's quick & easy.

Handling Apostrophes

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?

Nov 13 '05 #1
5 4076
replace the single apostrophes with double apostrophes and you're done.

Nov 13 '05 #2
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.

Nov 13 '05 #3
<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
Nov 13 '05 #4
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.

Nov 13 '05 #5
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?

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Lord Merlin | last post: by
3 posts views Thread by James Foreman | last post: by
4 posts views Thread by Nathan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.