Help needed with SQL string syntax in CommandText of PivotCache

I have a pivottable report based on external access query (get data..)
In VBA, the actual query is stored in the CommandText property of the PivotCache.


I need a function that will return a formatted SQL string.that CommandText property will recognise. Unfortunatly some criteria I have has also its own apostrophes

I have setup the following variables:

Dim MyCriteria as String
Dim strQueryName

strQueryName = "Query1"
MyCriteria = "'barbecue's'"

and need the following sql strings:

"SELECT * FROM strQueryName WHERE strQueryName.MyDateField BETWEEN #01/02/2009# and #28/02/2009# AND strQueryName.Type = MyCriteria"
"SELECT * FROM strQueryName WHERE strQueryName.MyDateField BETWEEN #01/02/2009# and #28/02/2009# AND strQueryName.Type = MyCriteria"

as in:



.CommandText = "SELECT * FROM strQueryName WHERE strQueryName.MyDateField BETWEEN #01/02/2009# and #28/02/2009# AND strQueryName.Type = MyCriteria"

MyCriteria = "Restaurant"

.CommandText = "SELECT * FROM strQueryName WHERE strQueryName.MyDateField BETWEEN #01/02/2009# and #28/02/2009# AND strQueryName.Type = MyCriteria"

This can also be related to

https://www.experts-exchange.com/questions/25025663/Trying-to-specify-a-range-with-sql-string-and-vba-variables.html

.If there is no function that i can use, can the above be converted into a usable string?