Live Scores
Shareware
Movie Stars
in
Access
Applications
(Entire Site)
Questions and answers to issues related to Microsoft: Windows, Applications, Development, Hardware, Server, Internet Protocols, Database, Exchange .
»
Applications
»
Microsoft Office
»
Access
»
need help with xor
need help with xor
I'm trying to use XOR in an Access 2007 query and I'm not getting the behavior I expected.
Given a table "Foo" with an ID field and text field "SomeText"
where the recrods are:
ID SomeText
1 a
2 a
3
4
If I query the "SomeText" field with:
Like "a"
I get records 1 and 2
if I query the "SomeText" field with:
IS NULL
I get records 3 and 4
if I query the "SomeText" field with:
Like("a") xor IS NULL
I get records 1 and 2 (which is what I expect)
but... when I query the "SomeText" field with:
Like("b") xor IS NULL
I don't get ANY records. Shouldn't I get records 3 and 4?
Solution: need help with xor
I think the requirement is to show all rows containing "a" or all rows containing Null when no rows contain "a".
Select Foo.*
from Foo, (Select count(*) cnt from Foo where sometext = "a") as bar
where foo.sometext = "a" or (foo.sometext is NULL and bar.cnt = 0)