CODEWITHSUNDEEP
sqlvb.netms-accessoledb
NapkinBob
NapkinBob

Reputation: 724

SQL String to query Access Database

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?

Upvotes: 0

Views: 563

Answers (1)

dfb
dfb

Reputation: 13289

You need to join the lookup table

SELECT * FROM MyTable INNER JOIN Lookup l on MyColumn = l.key and l.value =  'This Text String';

Upvotes: 1

Related Questions