difference between [not (a like b)] and [a not like b]

[muneco]

Attention Jet SQL experts,

I am looking for all rows in my_table where fld2 does not contain a comma. I have discovered that the following two SQL statements return a different set of rows using an Access 2000 query:

SELECT fld1 FROM my_table
WHERE not (((fld2) Like "*,*"

and

SELECT fld1 FROM my_table
WHERE (((fld2) not Like "*,*"

I would have expected them to be logically equivalent. Has anyone else experienced this, or can you please help me understand this? Thanks in advance for your help.

 

[WildHare]

I would have used the INSTR function to return the position of a comma, and selected against that:

SELECT fld1 FROM my_table
WHERE Instr([fld2], ",&quot = 0)


Gets around having to figure out the different between NOT(In...) and NOT IN(...), which always gives me a headache..



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[Zathras]

Can you give us a clue?

Is one result a subset of the other?

Are there rows that appear on one result and not the other and vice versa?

Can you provide specific examples of a "fld2" that is in one result and not the other?

Are the sql statements posted exactly the way they are run (since the parens are not balanced, I would guess that you have simplified the SQL that you are actually using -- what AND's and OR's do you have along with what you have shown?)

 

[muneco]

First of all, thank you kindly for both replies.

WildHare,
I was not aware of the "instr" function, thanks for educating me on that.

Zathras,
You are correct that I simplified the SQL. As it would happen, I am now having trouble duplicating the original pair of statements. But I now suspect that the source of my puzzlement was due to an additional condition (just as you inquired about!) that allowed two additional rows with null values through.

Thanks again to both of you gurus.