[sql] How to use "like" and "not like" in SQL MSAccess for the same field?

I would like to filter all the rows that are:

field like "*AA*" and field not like "*BB*"

But this is returning everything instead of showing all the rows that contains AA and doesn't contain BB.

well now it works like expected, just restarted ms access...

Sorry for my typo... :s updated

This question is related to sql ms-access where sql-like

The answer is


What I found out is that MS Access will reject --Not Like "BB*"-- if not enclosed in PARENTHESES, unlike --Like "BB*"-- which is ok without parentheses.

I tested these on MS Access 2010 and are all valid:

  1. Like "BB"

  2. (Like "BB")

  3. (Not Like "BB")


Simply restate the target field & condition;

where (field like "*AA*" and field not like "*BB*")

If you're doing it in VBA (and not in a query) then: where field like "AA" and field not like "BB" then would not work.

You'd have to use: where field like "AA" and field like "BB" = false then


Not sure if this is still extant but I'm guessing you need something like

((field Like "AA*") AND (field Not Like "BB*"))

what's the problem with:

field like "*AA*" and field not like "*BB*"

it should be working.

Could you post some example of your data?


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to ms-access

How to get parameter value for date/time column from empty MaskedTextBox Access And/Or exclusions Get length of array? How to pass an array to a function in VBA? How to insert values into the database table using VBA in MS access Access 2013 - Cannot open a database created with a previous version of your application Does VBA contain a comment block syntax? java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver Exception occurring. Why? Manipulating an Access database from Java without ODBC How to find longest string in the table column data

Examples related to where

Get only records created today in laravel Can I do Model->where('id', ARRAY) multiple where conditions? SQLSTATE[42S22]: Column not found: 1054 Unknown column - Laravel SQL Query with Join, Count and Where PHP MySQL Query Where x = $variable How to use NULL or empty string in SQL Rails: Using greater than/less than with a where statement CASE in WHERE, SQL Server How to use "like" and "not like" in SQL MSAccess for the same field? Can the "IN" operator use LIKE-wildcards (%) in Oracle?

Examples related to sql-like

SQL Server: use CASE with LIKE Create hive table using "as select" or "like" and also specify delimiter How do I find ' % ' with the LIKE operator in SQL Server? Using LIKE operator with stored procedure parameters SQL- Ignore case while searching for a string Is the LIKE operator case-sensitive with MSSQL Server? Using Eloquent ORM in Laravel to perform search of database using LIKE SQL 'LIKE' query using '%' where the search criteria contains '%' How to use "like" and "not like" in SQL MSAccess for the same field? MySQL SELECT LIKE or REGEXP to match multiple words in one record