I'm applying a filter to a recordset. The filter contains a variable that may contain an apostrophe. The filter works until a variable value contains an apostrophe. I've tried replacing the single quotes in the filter string with two double quotes without success. I've also tried using Chr(34) in place of the double quotes.
When there is an apostrophe in the variable rstAreaCategories!Category the run-time error will occur. Under the original code, when this variable evaluates to plant tree the filter works fine. When the variable evaluates to We'll need to plant a tree, an error will occur because of the apostrophe.
Here's the original code which I loop through:
strFilter = "JobArea = '" & rstAreaCategories!Area & "' AND Category = '" & rstAreaCategories!Category & "'"
Here are the lines I've tried:
strFilter = "JobArea = " & Chr(34) & rstAreaCategories!Area & Chr(34) & " AND Category = " & Chr(34) & rstAreaCategories!Category & Chr(34)
strFilter = "JobArea = """ & rstAreaCategories!Area & """ AND Category = """ & rstAreaCategories!Category & """"
These attempts evaluate the filter string to
"JobArea = "Bathroom - 1" AND Category = "plant tree""
...and I get a run-time error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with on another."
In this particular case there is not an apostrophe in plant tree and therefore the filter works. When the value of rstAreaCategories!Category is We'll need to plant a tree the error occurs. I'm looping through the categories. When I hit the latter category the same run time error occurs as when I try to change the filter string to adjust the quotes.
Not sure where I'm going wrong but any help would be appreciated.