Quotes in strings

[jsic1210]

Hello,
I have a form that filters a report. Users have the option to filter the report based on text in a certain field (the field is ExceptionName). So the user can type in Index, and any record with Index in the ExceptionName field will appear. The field used to filter the report is a combobox based on a query. Some of the options in the filter have quotes in them. So if the user selects "Customer" replaced by "Client," it produces an error because the Where statement reads:
ExceptionName Like "*"Customer" replaced by "Client"*"
It cannot read this Where statement because of the quotes around Customer and Client. I tried "" and ' in my VBA, but neither work. Can anyone help?
If this is isn't confusting.
Thanks,
Jeff

 

[vbaInet]

Are you saying the value that the user selects has quotes in them? Why not get rid of the quotes behind the scenes before using it in the filter?

 

[jsic1210]

Yes, that's what I'm saying.
I think you're suggesting changing the text in the table, but I can't actually do that, as it's exact language from the contract.

 

[vbaInet]

Nope, I wouldn't suggest that

Is the search supposed to return records that contain quotes as well?

 

[jsic1210]

Yes. So in the example I gave, there will probably only be one record with that specific string, but we would need that record to come up in the report, with the quotes.

 

[vbaInet]

Use the Replace() function to double up the quotes.

 

[jsic1210]

I tried doing this in the VBA code, but as soon as I finished writing that line of code, I received an error message that says: Compile error: Expected list separator or ).
Here is my code:

'Set strWhere for Name
If Nz(Me.txtName, "") <> "" Then
    strWhere = strWhere & " AND ExceptionName Like ""*" & Replace(Me.txtName,""","""") & "*"""
    'Strip off AND
    If Left(strWhere, 5) = " AND " Then strWhere = Mid(strWhere, 6)
End If

 

[vbaInet]

Let's get the quotes part working first before you start building the other parts:

strWhere = "ExceptionName Like *" & Replace(Me.txtName, Chr(34), String(2, Chr(34))) & "*"

 

[jsic1210]

I actually built this code a while back, but realized this potential problem today, so that's why there's more to it.
I was able to get it working, but I just had to slightly tweak your code:

strWhere = "ExceptionName Like ""*" & Replace(Me.txtName, Chr(34), String(2, Chr(34))) & "*"""

Notice the set of double quotes before an after the asterisks. Thank you so much for your help!

 

[vbaInet]

Yep, hard to see it when I'm typing on here.