How to Filter For Criteria With Single Quotes in the String

[deekadelic]

I'm trying to write a query that will generate a field from a table where the value in field "CName" contains either "Bills" or "Bill's", but I'm getting wacky results, for instance: records where neither "Bill", "Bills", "Bill's", nor any other value containing a single quote is contained in the field in question. I've narrowed the query down through enough trial and error to know that it is this value of "Bill's" that is causing the problem. But I don't know what syntax the query is looking for to identifty those records, nor how this syntax error could be resulting in some of the records I'm seeing.



Here's the query code for this field:

Customer_NEWEST: IIf([tbl_CUSTOMER_AD_HOC_REPORT]![CName] Like "*Bill's*" Or "*Bills*","BILLS","OTHER")

Please advise. Thank you

- Deek

[JoeM]

Should "Bill" be included?
If so, then just look for "Bill", as that would be included in all those different options.

[ssanfu]

The short answer is you have to double up on the single quote.

Customer_NEWEST: IIf([tbl_CUSTOMER_AD_HOC_REPORT]![CName] Like "*Bill''s*" Or "*Bills*","BILLS","OTHER")




-----------------------------------------------------------
I have a function that I use in queries to automatically double the single quote in a word.

Function ConvertQuotesSingle(InputVal)
   ConvertQuotesSingle = Replace(InputVal, "'", "''")
End Function

[deekadelic]

The thing with "Bill" is that there are other values such as "Bill Gunderson" that I do not want this query to capture.

[John_G]

The condition part of your Iif is incorrect. You can't use something like "If x = 1 or 2". We humans can make sense out of it, but Access can't - you need to say if X = 1 OR X = 2, so your statement has to be:

Customer_NEWEST: IIf([tbl_CUSTOMER_AD_HOC_REPORT]![CName] Like "*Bill's*" Or [tbl_CUSTOMER_AD_HOC_REPORT]![CName] Like "*Bills*","BILLS","OTHER")

[ssanfu]

Oops, I missed that.

Good catch John_G.

[deekadelic]

That did the trick!

Thank you!