Am I going mad with <> operator?

[Tracy]

I am trying to do the simplest thing adn I think I must be doing something v v v silly.

Dim rs as new AdoDB.recordset

rs.open "SELECT * FROM qry1 WHERE TextField1 <> 'Tracy'", currentproject.connection

Unfortunately it return EOF. It seems it doesn't like the <> operator, nor !=, nor 'NOT'. When I use = it works fine.

Please please can anyone help me by explaining what operator I can use to select records that do are not equal to a particular string?

Many thanks
TRacy

 

[FoFa]

That is correct, <> is the not equal operator.
Did you post your actual SQL, or an example? It might be something else making it appear like it is not working.

 

[Tracy]

SQL (within VB) above.

I have found now why it doesn't like it:

In my SQL I say "SELECT * FROM qry1 WHERE TextField1 <> 'Tracy'"

but I need an nz, so it should read:

"SELECT * FROM qry1 WHERE Nz(TextField1) <> 'Tracy'"

so it can't compare null 'not equal' to 'Tracy', but it can compare null 'equal' to 'Tracy'

Bizarre!

Thanks anyway FoFa

 

[FoFa]

True, because NULL is "special". When you compare 'Tracy' to NULL, it does not match, but if you compare <> 'Tracy' To Null, it does not match. There is a system switch you can set that changes this behavior (not sure which one off the top of my head) but I always use the IS NOT NULL check along with it as it becomes more noticable in the actual SQL.