Query with <> and wildcard

  • Thread starter Thread starter Maileen
  • Start date Start date
M

Maileen

Hi,

I wrote a simple table with few records inside.
I want to select all records where 'test' string doesn't exist.
for that i wrote the following query but it returns me an empty set
everytime. And i'm sure that some data should be returned.

Query :
SELECT *
FROM TestTable
WHERE TestName <> '%test';

it works under Oracle or MS SQL, so why it doen't work under MS Access
2002 ?

thanks,
Maileen
 
Maileen said:
Hi,

I wrote a simple table with few records inside.
I want to select all records where 'test' string doesn't exist.
for that i wrote the following query but it returns me an empty set
everytime. And i'm sure that some data should be returned.

Query :
SELECT *
FROM TestTable
WHERE TestName <> '%test';

it works under Oracle or MS SQL, so why it doen't work under MS Access
2002 ?

thanks,
Maileen

Maileen,

Try:

SELECT *
FROM TestTable
WHERE TestName <> '*test';


Sincerely,

Chris O.
 
If no information exists try this: IsNull([TestName]) it will catch all
records with no information.
 
Maileen said:
Hi,

I ... wrote the following query but it returns me an empty set
everytime. And i'm sure that some data should be returned.

Query :
SELECT *
FROM TestTable
WHERE TestName <> '%test';

it works under Oracle or MS SQL, so why it doen't work under MS Access
2002 ?

Two reasons:

The = operator ignores wildcards; you need to use the LIKE operator to have wildcards recognized (I believe that this is also true in SQL and Oracle, or it was the last I looked!)

Access use * as the "match any string" wildcard instead of %.

Try

WHERE TestName NOT LIKE '*test';
 
Back
Top