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.

Answer 1

Try:

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

Use an asterisk for the wildcard character.

Answer 2

See the discussion of wildcard characters at this page to understand when and why you must use * and ? instead of % and _.

Another option is to use the ALike comparison, which always expects ANSI-92 wildcards.

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

However, ALike is not supported by databases other than Access.

Answer 3

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.