First, I have been studying this related stack post, but it hasn't been able to solve my problem.
When I run the following query inside the MS Access 365 query window, I get 97 records as expected:
SELECT *
FROM Prices
WHERE OptionID Like '*_*'
My objective was to return all records in which the OptionID contains an underscore. Success.
When I run the following query inside the MS Access 365 query window, I get 109,967 records as expected:
SELECT *
FROM Prices
Only 97 of my OptionIDs have an underscore in them, and thus the where clause in the first query above restricts the recordset to 97 records, as expected.
Here's where it gets tricky.
When I run this query in my C# app, connecting to Access via OleDb:
SELECT *
FROM Prices
WHERE OptionID Like '*_*'
...I get 0 records returned. The reason is because Access SQL via OleDb must use % instead of * when wildcarding. So then I do this:
SELECT *
FROM Prices
WHERE OptionID Like '%_%'
...and I get 109,967 records. As if the where clause was ignored! What I believe is happening is that OleDb is interpreting the underscore as a single-character wildcard itself, and thus no filtering is occurring.
And thus my problem in summary: I cannot figure out how to tell Access (via OleDb) that I want to use the underscore as a literal and not a wildcard, surrounded by % in a wildcard query. How is it done? (I tried escaping the underscore with a \ and \\, but no dice.)
Put the underscore inside a "character range", delimited by square brackets. Access will then understand you want it treated as the literal underscore character instead of as the ANSI 92 mode (which you're using with OleDb) wildcard which matches any single character.
WHERE OptionID Like '%[_]%'