Solved Like operator no longer works in ACCESS query criteria

[LarryE]

I am experiencing a very very strange event when I try to use the Like Operator in a query. I have a table of vendor names with Vendor A, Vendor B and Vendor C in the VendorName field. I want to show only thos vendors that are do not have "B" in their name, so I used:

SELECT DISTINCTROW Vendor.VendorID, Vendor.VendorName, Vendor.VendorContact, Vendor.VendorStreet, Vendor.VendorCity, Vendor.VendorState, Vendor.VendorPhone, Vendor.VendorEMail, Vendor.VendorPostalCode
FROM Vendor
WHERE (((Vendor.VendorName) Not Like "*B*"))
ORDER BY Vendor.VendorName;

ACCESS automatically added the letter A to the Like:

SELECT DISTINCTROW Vendor.VendorID, Vendor.VendorName, Vendor.VendorContact, Vendor.VendorStreet, Vendor.VendorCity, Vendor.VendorState, Vendor.VendorPhone, Vendor.VendorEMail, Vendor.VendorPostalCode
FROM Vendor
WHERE (((Vendor.VendorName) Not ALike "*B*"))
ORDER BY Vendor.VendorName;

I have never ever seen this before and it renders the query useless. This has only happened since Office 365. I have never seen this in previous versions.

Comments encouraged.

 

[CJ_London]

You are using a different version of sql - see file>options. Think it is called ansi92 (not at my computer right now)

 

[plog]

I have an answer, but not one that makes sense. It just made me more frustrated the more I tried to learn. Below is the fix for Access:

Redirecting

ALIKE is part of ANSI SQL 92, although when I google 'ansi sql 92 keywords' not a single resulting page lists it. You can find random stack overflow pages and even sites on this forum that say that ALIKE is just like LIKE except it works with % symbols as wildcards instead of the * symbol. That's it? Really?

So, I tried it myself and that's the only difference I noticed.

...WHERE SomeField LIKE 'B*'... --- it returned the expected results
...WHERE SomeField LIKE 'B%'... --- it returned no results
...WHERE SomeField ALIKE 'B%'... --- it returned the expected results
...WHERE SomeField ALIKE 'B*'... --- it returned no results

This is going right after FIRST and LAST on my list of horrible ACCESS keywords.

 

[CJ_London]

Ansi92 is old (guess 1992?) and I believe was there for ADP's and is still there for backwards compatibility. Ansi92 uses the % as a wildcard instead of *. It's more related to T-SQL

Not surprised you can't find anything I suspect relevant pages were retired years ago - try and find anything more than 4/5 years old can be a challenge.

I did find this https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

 

[LarryE]

OK
SELECT Vendor.VendorName
FROM Vendor
WHERE (((Vendor.VendorName) Not ALike "%B%"));
worked. It still add the A to Like, but using the % in place of the * worked so far.
Thanks to all who responded. I would have never been able to figure this out.

 

[plog]

I did find this https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Oh, I found lots of ANSI92 stuff and like your link they all had 0 results when searched for 'ALIKE'

 

[cheekybuddha]

Thanks to all who responded. I would have never been able to figure this out.

Really the answer it's too just uncheck the use ANSI92 SQL checkbox.

Then you will be back to normal

 

[LarryE]

Really the answer it's too just uncheck the use ANSI92 SQL checkbox.

Then you will be back to normal

OK I did that and the * wildcard works again. Thanks.
Is it best to keep the ANS192 SQL the active mode? It doesn't really matter to me if I use % or * as the wildcard symbol. If it is best to keep it, I can do that. Just wondering. I know nothing about that kind of thing.

 

[Pat Hartman]

That isn't the only change as you found out. Plus ANSI 92 SQL is really old. There is a far newer standard. If most of your BE's are SQL Server, and you write a lot of T-SQL, leaving the checkbox checked will keep you from having to worry about wild cards but that's about all.

I don't know if this is a database specific setting. I think it is probably an "all databases" setting. If that is the case, you will always have problems with your old databases and other people's samples.

 

[LarryE]

That isn't the only change as you found out. Plus ANSI 92 SQL is really old. There is a far newer standard. If most of your BE's are SQL Server, and you write a lot of T-SQL, leaving the checkbox checked will keep you from having to worry about wild cards but that's about all.

I don't know if this is a database specific setting. I think it is probably an "all databases" setting. If that is the case, you will always have problems with your old databases and other people's samples.

Ok well I'll just leave it off. Thanks

 

[isladogs]

I don't know if this is a database specific setting. I think it is probably an "all databases" setting. If that is the case, you will always have problems with your old databases and other people's samples.

It can be either just the current database or the default for all new databases.

Either way, the setting doesn't affect other existing databases
As others have said, its wise to leave these settings unchecked

 

[LarryE]

It can be either just the current database or the default for all new databases.
View attachment 116541
Either waty, the setting doesn't affect other existing databases
As others have said, its wise to leave these settings unchecked

Then that is what I will do. Thanks to all.

 

[DickyP]

If you are really worried you can put a line in any code that runs at start up:

SetOption "ANSI Query Mode", 0