Single Apostrophe in a VBA code; syntax error

[HSXWillH]

I have a field in a database that contains last names. In some of those names, like O'Brien and O'Connor, there is a ' symbol.

I am using combo boxes on a form to build a form filter and in particular, I receive a Runtime 3075 Syntax Error : Missing Operator in query expression '([Player_Name] Like '*Jim O'Toole*')' and I know it stems from the apostrophe in the name box, but I don't know how to fix it.

The combo box that contains the list of names is cboPlayer. Here is my VBA code as current with non-related details omitted. I have a cboActions box that contains what output I wish to have based on the combo-entries. I have a command button Perform that executes the following code upon Click. How do I modify the code to account for any data-necessary apostrophes?

Private Sub Perform_Click()
    Dim SCstrFilter As String

    'cboPlayer - Hard Text
    If Me!cboPlayer > "" Then _
        SCstrFilter = SCstrFilter & " AND ([Player_Name] Like '*" & Me!cboPlayer & "*')"

    'Tidy up results and apply IF NECESSARY
    If SCstrFilter > "" Then SCstrFilter = Mid(SCstrFilter, 6)

    If cboActions = "Add Stock Items" Then
        DoCmd.OpenForm "Catalog", , , SCstrFilter   (This is where the VBA debug window points to with the error)
    End If

[ChipR]

You can use quotes to delimit a string with an apostrophe inside. To put a quote in a string, use two quotes in a row, and the string will contain one. This works:

..." AND Player_Name Like ""*" & Me.cboPlayer & "*"")"

[HSXWillH]

Thank you; that works perfectly.

[NeoPa]

Check out Quotes (') and Double-Quotes (") - Where and When to use them.

Doubling up each is always an option when dealing with a quote character embedded in a value. Indeed this is recommended above the sometimes suggested alternative of treating the different types of quotes as interchangeable (which only ever seems to be the case within a Microsoft environment). There is nothing anti-MS about this. It is simply about fundamental logic and portability of code (Always a good idea, even when not planning on taking advantage of it).

[OldBirdman]

Because a name can have either type of quote, a more general solution should be considered. If names like Pete O'Toole or Tom "Bud" Adams can occur, all methods shown will fail.

How about -

"Player_Name Like '*" & Replace(Me.cboPlayer, "'", "''") & "*'"

Note: Replace(#1, #2, #3) arguments are:
#1: Control or Variable
#2: One single quote between double quotes
#3: Two single quotes between double quotes

[NeoPa]

all methods shown will fail.

I'm confused OB.

Two main reasons :

1. Your solution only doubles up the single-quotes. This isn't a problem, as discussed below. However you indicated earlier (quoted text) that this wouldn't work.
2. Your solution is a perfect example of the solution I had proposed in my previous post.

What is strange is that actually this solution does work in all circumstances. There is no need to handle double-quotes specially, as you used the correct (standard) quotes around the string in the first place. Thus the double-quotes are treated without ambiguity by the parsing engine.

As a clarification for anyone still unconvinced about using the standard quotes (after all Access adds the non-standard ones in for you in many places) if this were to be done using these (double-quotes) instead, then OB's code would look like :

"Player_Name Like ""*" & Replace(Me.cboPlayer, """", """""") & "*"""

I hardly need say that I don't recommend this approach, but it may be helpful for those still struggling with the concept but unwilling to take on the standard approach.

NB. All quotes in this example are Double-Quotes.

[OldBirdman]

When I substitute both my names, Pete O'Toole or Tom "Bud" Adams, into ChipR's formula, I get:

(Player_Name Like "*Pete O'Toole*")

which is valid, but:

(Player_Name Like "*Tom "Bud" Adams*")

which is not a valid string.

1. Your solution only doubles up the single-quotes. .

.
True. But the string is delimited with single quotes, so there is no reason to double the double-quote. Actually, it would be an error to do so.

2. Your solution is a perfect example of the solution I had proposed in my previous post.

I don't see a solution in your post. Looking at your link, I cannot find where it addresses inserting a string into another string where either the single- or double-quote may occur in the inserted string. It does address either the single-quote within single quotes, and double-quotes within double quotes. I was proposing code that would do what this article infers.
I entered this thread because I felt the solution given left a condition unresolved. That condition was a name with a double-quote within it. My telephone book, latest election ballot, and my personal address book have names that have single-quotes and names with double-quotes. I could not see why HSXWillH might not have such names in the future, although apparently not yet.
I believe that testing should include as many conditions as the developer can think of, and not just the conditions that exist currently. I did not think that HSXWillH had thought of the name list containing the double-quote. Therefore, the statement

Thank you; that works perfectly.

after ChipR's post really should be

Thank you; that works perfectly for the example given.

[NeoPa]

I suppose that makes sense if you didn't see a recommended solution in my post. Let me be more explicit :

If you look at the linked article you will find the point expressed clearly in the section titled Embedded Quotes (General), albeit the example given was not specifically for SQL, the text explains the concept that works generally regardless of which quote character is used.

In the post itself, I say

Doubling up each is always an option when dealing with a quote character embedded in a value.

No example is given clearly, but the concept is expressed immediately after the link to the article where there is a usable example.

Lastly, it seems you think I was critical of the solution you proposed. That's entirely not what I was trying to say. I was saying indeed that it's perfect. I simply commented that it was a reiteration of what I'd already posted. Not a problem. A useful example was provided, but I was simply confused at your comment that previous solutions were wrong, which I find difficult to comprehend in the circumstances.