How to fix an error where no records come up if the phrase has an apostrophe in an MS access database?

Question

I have a question about how to submit queries that include an apostrophe in an access database. For example, I want to search for records that have "women's health" in the title. When I try to search that, no records come up even though there are records that include that phrase. If I search women health, the records come up. How can I overcome the problem with the apostrophe?

I've tried searching previous questions and most of them say that I should use " " in my SQL query but I am using that and it still doesn't solve the problem. I don't have a technical background, can anyone give me some advice on how to fix my query so that I can search with apostrophes? My query is below. Thanks!

SELECT [Off-site records].[File Name], [Off-site records].Unit, 
    [Off-site records].Branch, [Off-site records].Division, 
    [Off-site records].[Date Sent], [Off-site records].[Archives (Yes/No)], 
    [Off-site records].[Date range], [Off-site records].[Accession Number], 
    Off-site records].[Box #], [Off-site records].[File Name], 
    [Off-site records].ID, [Off-site records].[File Name], 
    [Off-site records].[File Name]
FROM [Off-site records]
WHERE ((([Off-site records].[File Name]) Like "" & Forms!Search![File name] & "") 
    And (([Off-site records].Unit) Like "" & Forms!Search!Unit & "") 
    And (([Off-site records].Branch) Like "" & Forms!Search!Branch & "") 
    And (([Off-site records].Division) Like "" & Forms!Search!Division & "") 
    And (([Off-site records].[Date Sent]) Like "" & Forms!Search![Date sent] & "") 
    And (([Off-site records].[Archives (Yes/No)]) Like "" & Forms!Search!Archives & "") 
    And (([Off-site records].[Date range]) Like "" & Forms!Search![Date range] & "") 
    And (([Off-site records].[Accession Number]) Like "" & Forms!Search![Accession number] & "") 
    And (([Off-site records].[Box #]) Like "" & Forms!Search![Box #] & "") 
    And (([Off-site records].[File Name]) Like "" & Forms!Search![File name 2] & "") 
    And (([Off-site records].[File Name]) Like "" & Forms!Search![File name 3] & "") 
    And (([Off-site records].[File Name]) Like "" & Forms!Search![File name 4] & ""));

Your SQL is very long and somewhat complicated. Can you reduce it to the shortest possible that still shows the problem? Can make all the difference in your trouble shooting. — Smandoli, Jul 23 '13 at 22:45
"Women's Health" is not in the SQL you posted. Can you show us a query that includes it, please? — Smandoli, Jul 23 '13 at 22:47
Is this query run from a VBA module? Please post VBA, if so. — Smandoli, Jul 23 '13 at 22:50

score 0 · Answer 1 · edited May 23 '17 at 12:21

Instead of this:

Like "" & Forms!Search![File name] & ""

Try this:

Like " & chr(34) & Forms!Search![File name] & chr(34) & "

That is, replace every double-quote mark that belongs in the final SQL with the ASCII equivalent.

This may fix your problem. Even if it doesn't, it is much better practice. Note it pertains specifically to SQL in VBA code.

To trouble shoot queries in Access, you must simplify your query as much as you can for testing. If it fails with an inserted variable, rig it as a static query and get it to work. If it fails in VBA, turn it into a query object and get it to work that way. (Use Debug.Print to grab the final SQL code that is actually run.) Simplify, solve, and then work your way back to the (frightfully) complicated result.

By the way, you can start simplifying by removing every instance of [Off-site records].. You've set the table name in the FROM clause, and because there is only one source, all the other uses of [Off-site records]. are not needed.

How to fix an error where no records come up if the phrase has an apostrophe in an MS access database?

1 Answers1