I'm having a little trouble with the command line of a oledb query on an access database. I've tried a number of different syntaxes, both right in access and in my code. The connection to the database is fine, the error comes when I try to fill the da, or when I run it as a query in access.
SELECT * FROM MyTable WHERE MyColumn = 'This Text String';
When I run this in acces, I get a Data Type mismatch. Double quotes yields the same. I thought it was because of the spaces do I tried wrapping the Text String in square brackets thusly:
SELECT * FROM MyTable WHERE MyColumn = [This Text String];
In Access this prompts "Enter Parameter Value" listing the parameter as "This Text String".
I've also tried using the Like operator:
SELECT * FROM MyTable WHERE MyColumn LIKE 'This Text String';
Whether with single or double quotes, this produces no errors, but also produces no results. I've Tried CONTAINS which gives me a syntax error.
At this point I don't know what else to try. what I'm trying to accomplish is:
Return all the rows in 'MyTable' where the text in 'MyColumn' equals 'This Text String'
Can someone help me? I feel I'm overlooking something, possibly obvious.
Edit: Ahh... I'm not sure if this is the problem, but perhaps it will help.
I just realized that the column (MyColumn) that I'm trying to filter based upon, is not just plain text in access, it's actually a lookup of a column in another table.
Pehaps that's the issue, because the data type is not actually text, it's number (since it's a list). Ok... how do I overcome that?
You need to join the lookup table
SELECT * FROM MyTable INNER JOIN Lookup l on MyColumn = l.key and l.value = 'This Text String';