Why doesn't SQL LIKE work in Microsoft Access?

Question

I want to my make a search-statement and query things like this

select * from table where col like '%vkvk%' 

But with trial and error I've come to the conclusion that access doesn't work with LIKE or wildcard operators. Does anybody have some other solutions because I ain't so in to access actually, so I really don't know.

Apfel

Answer 1

Try:

select * from table where col like '*vkvk*'

Use an asterisk for the wildcard character.

Tuneberg

Answer 2

If you want to use some SQL syntax that is like SQL Server, go to your Access OPTIONS and set it for "SQL 92" mode. So far as I know, this does two main things (there may be others):

1. allows you to use % and _ as wildcards instead of Jet SQL's * and ?.

2. allows you to use the non-crazy derived table syntax:

   SELECT MyTable.*
   FROM (SELECT * FROM SomeTable) As MyTable
   

...instead of the bollixed-up Jet method:

   SELECT MyTable.*
   FROM [SELECT * FROM SomeTable]. As MyTable

...which has problems with table and field names with spaces in them, since you have to use brackets inside the derived table definition, which breaks the Jet syntax entirely.

As I said, there may be other things it changes, but if you're a SQL Server programmer, you may find it easier to set SQL 92 mode on. On the other hand, most Access help uses Access/Jet/ACE conventions, so you may end up more confused by trying to use it.

EDIT:

Since originally posting this, I've discovered that there are problems with turning on SQL 92 mode in an existing Access application. The two I discovered were:

1. It changes the list of reserved words, which means that SQL that previously worked with the SQL 89 list of reserved words can break (if it uses a SQL 92 reserved word).

2. It can break multi-column combo boxes with a hidden first column (which is a very common UI object in Access applications). Specifically, it breaks the Autoexpend/autoselect behavior.

There may be other problems, but I discovered these accidentally when I turned on SQL 92 mode in a client project to test something for SO and forgot to turn it off when I distributed the next update. Fortunately, the problems were quickly detected, and it didn't take me too long to idenfity SQL 92 mode as the cause of the problems.

In short, I don't consider SQL 92 mode in Access to be of use to anybody at all. It's a feature aimed at people who won't be using Access interactively in the first place, seems to me.

Tracheotomy

Answer 3

like '%kvk%' sometimes work sometimes don't

With Access 2010 with sql server (2008) linked tables

Use '*kvk*'

Murraymurre

Answer 4

I tried several "Like" syntax on one request, (I'm using VB.NET and a MS-ACCESS 2010 database), and none of them could get any other result than throwing an exception.Why? I'm not having any idea about that.

I did this workaround that could be useful on some similar cases: Instead of

SELECT dbFieldDisplayName FROM dbTableName WHERE dbFieldSearchName Like 'A*'

I Used:

SELECT dbFieldDisplayName FROM dbTableName WHERE dbFieldSearchName >='A' AND dbFieldSearchName <'AZZZ'

Sweepstakes

Why was my previous answer deleted? Not written in a correct English? I know I did several mistakes on it, but I'm French, would like me to answer in French, instead ? It would be great to be adivised about edit from reviews before being deleted. – Sweepstakes

Because this should be a new question, not an answer to this question. It would be better to figure out why your LIKE was not working, instead of using a (kinda) clunky workaround. – Malta

Ok, starting a new topic. – Sweepstakes