I have an application in which my users sometimes use an apostrophe in their
entries. This really causes me alot of problems in queries and reports,
because it thinks its half a quote or something. Is there a way to handle
apostrophes in data? At the moment, I have a person in the database named
O'Donnell. I can't seem to run reports about him, or anyone else with the O'
convention in their name. If I take out the apostrophe, I can't match his
name to do the reports, because obviously O'Donnell doesn't match ODonnell.
I have many more names like this. Any ideas?

Thanks
Mike

You will need to use double quotes instead of single quotes to delimit the string, but how
you do that will depend on exactly what you are doing.

Here is a quick example of what needs to be done.

Public Function TestQuotes()
Dim strTextString
strTextString = "O'Hare"
TestQuotes = "SELECT [JobFile].[Name] FROM JobFile WHERE [JobFile].[Name] LIKE """ &
strTextString & """;"
End Function

In the debug window type
?TestQuotes
to get the return value what you will get back is
SELECT [JobFile].[Name] FROM JobFile WHERE [JobFile].[Name] LIKE "O'Hare";

You can also use Chr(34) concatenated in instead of the multiple double quotes. Chr(34) is
a double quote. I'm not very good at counting the double quotes, I usually just have to
play with them until I get them correct. Sometimes it takes 3 and sometimes it takes 4
(""" or """").

--
Wayne Morgan
Microsoft Access MVP


"Mike" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have an application in which my users sometimes use an apostrophe in their
> entries. This really causes me alot of problems in queries and reports,
> because it thinks its half a quote or something. Is there a way to handle
> apostrophes in data? At the moment, I have a person in the database named
> O'Donnell. I can't seem to run reports about him, or anyone else with the O'
> convention in their name. If I take out the apostrophe, I can't match his
> name to do the reports, because obviously O'Donnell doesn't match ODonnell.
> I have many more names like this. Any ideas?
>
> Thanks
> Mike
>
>

In a SQL string, you need to replace any occurrence of an apostrophe with
two apostrophes. In Access 2000 and later, the built-in Replace() function
makes this easy. Here's an example from a current project ...

Set rst = db.OpenRecordset("SELECT Count(*) AS TheCount FROM tblMessage
WHERE MessageText = '" & Replace(Me!txtMessageText, "'", "''", 1, -1,
vbBinaryCompare) & "' AND MessageID <> " & Me!MessageID)

For more information and some alternative approaches to the problem, see the
help topic 'Quotation Marks in Strings'. In Access 2000 and later, to find
this topic make sure to start your search from the VBA window, not the
Access window.

--
Brendan Reynolds (Access MVP)
(E-Mail Removed)

"Mike" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I have an application in which my users sometimes use an apostrophe in
their
> entries. This really causes me alot of problems in queries and reports,
> because it thinks its half a quote or something. Is there a way to handle
> apostrophes in data? At the moment, I have a person in the database named
> O'Donnell. I can't seem to run reports about him, or anyone else with the
O'
> convention in their name. If I take out the apostrophe, I can't match his
> name to do the reports, because obviously O'Donnell doesn't match
ODonnell.
> I have many more names like this. Any ideas?
>
> Thanks
> Mike
>
>