Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1235
  • Last Modified:

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     <null>
4     <null>

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?
0
gothamww
Asked:
gothamww
  • 13
  • 9
  • 6
  • +3
3 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

  The problem is that on the Like("b"), it returns a NULL.  From the on-line help on XOR:

"If one, and only one, of the expressions evaluates to True, result is True. However, if either expression is Null, result is also Null. When neither expression is Null, result is determined according to the following table:"

JimD.
0
 
GRayLCommented:
This from help

 
Xor Operator
     

Used to perform a logical exclusion on two expressions.

Syntax

[result =] expression1 Xor expression2

The Xor operator syntax has these parts:

Part Description
result Optional; any numeric variable.
expression1 Required; any expression.
expression2 Required; any expression.



Remarks

If one, and only one, of the expressions evaluates to True, result is True. However, if either expression is Null, result is also Null. When neither expression is Null, result is determined according to the following table:

If expression1 is And expression2 is Then result is
True True False
True False True
False True True
False False False



The Xor operator performs as both a logical and bitwise operator. A bit-wise comparison of two expressions using exclusive-or logic to form the result, as shown in the following table:

If bit in expression1 is And bit in expression2 is Then result is
0 0 0
0 1 1
1 0 1
1 1 0

0
 
dqmqCommented:
Because of the how nulls work NULL Like "b"  does not evaluate to false.


Tows 1 and 2:
"a" Like "a"  ==> TRUE
"a" IS NULL  ==> FALSE
XOR                      TRUE

Rows 3 and 4:
NULL Like "b"  ==> NOT TRUE
NULL IS NULL  ==> FALSE
XOR                         NOT TRUE
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GRayLCommented:
It depends how you wrote the query.  Can you post the SQL here?
0
 
dqmqCommented:
The GrayL discussion is based on two-valued logic.  But in SQL we have 3-valued logic, because NULL = NULL is neither true/false.

0 XOR 0 = False
0 XOR 1 = True
0 XOR ? = ?
1 XOR 0 = True
1 XOR 1 = False
1 XOR ? = ?
 




0
 
dqmqCommented:
Left out a piece:

0 XOR 0 = False
0 XOR 1 = True
0 XOR ? = ?

1 XOR 0 = True
1 XOR 1 = False
1 XOR ? = ?

? XOR 0 = ?
? XOR 1 = ?
? XOR ? = ?
 
0
 
GRayLCommented:
As I said it depends on how he wrote the query:

SELECT flda FROM myTable WHERE fldb="b" XOR ISNull(fldb)

is different from

SELECT flda FROM myTable WHERE fldb = "b" XOR Null
0
 
Gustav BrockCIOCommented:
> Like("b") xor IS NULL

You logic seems cribled. If [SomeText] is "b" it is not Null, and if it is Null it makes no sense to bother if it could be "b".

So it could be:

where [SomeText] = "b" Or [SomeText] Is Null

/gustav
0
 
gothamwwAuthor Commented:
I used the Access interface to write the query.  Here's the SQL:

SELECT fooXor.ID, fooXor.SomeText
FROM fooXor
WHERE (((fooXor.SomeText) Like ("a") Xor (fooXor.SomeText) Is Null));

What should it be to return the NULL records?  or is this impossible?
0
 
Gustav BrockCIOCommented:

WHERE fooXor.SomeText = "a" OR fooXor.SomeText Is Null;

/gustav
0
 
gothamwwAuthor Commented:
cactus_data -

WHERE fooXor.SomeText = "a" OR fooXor.SomeText Is Null;

gives me all the records 1, 2, 3 and 4.

If I'm searching with "a", what I need is just "a" records (and no null records)

If I'm searching for "b" I need just null records.

In other words - if there's a match or "a" or "b", I want only the records that match.  If there's no match, then I want only the null records.
0
 
GRayLCommented:
WHERE Isnull(fooXor.SomeText)  -   will return the null records
0
 
Jeffrey CoachmanMIS LiasonCommented:
gothamww,

I am left wondering why you decided to use XOR in the first place?

Personally, this is actually the first post using XOR I have seen in 4 years in the Access zone.

Given the extensive disscussion you have sparked, are you *absolutely sure* that there is not a simpler alternative?

JeffCoachman
0
 
GRayLCommented:
Hi Jeff:  I gave it - I think.
0
 
GRayLCommented:
Seen the light!

WHERE SomeText IN ("a","b",Null)
0
 
gothamwwAuthor Commented:
GRayL:

> WHERE Isnull(fooXor.SomeText) will return the null records - yes.

But what I'm looking for is:

if there's a match for a, return all records matching a.  if not, return all null records.
0
 
gothamwwAuthor Commented:
GRayL:

> WHERE SomeText IN ("a","b",Null)

this returns both records that match "a" and the null records as well.  I was looking for either one, or the other.
0
 
gothamwwAuthor Commented:
actually, the more I think about this, the more I realize it's impossible to do in one query, and I was misunderstanding what xor does, and how queries work.

I was trying to say: give me all records that match "b" or all records that are null, but not both - which on the surface sounds like xor.

but actually there's no way for the query to know when it is scanning one record at a time whether there are any matches elsewhere in the record set for "b" - seem like I need to match for "b" first, then, if that query comes up empty, check for null records.  It's just not something you can do in one WHERE clause - that makes sense, right?
0
 
GRayLCommented:
Maybe this:

WHERE SomeText IN ("a","b",Nz(SomeText,""))
0
 
GRayLCommented:
Maybe this:

WHERE SomeText IN ("a","b",Nz(SomeText,""))
0
 
GRayLCommented:
Maybe this:

WHERE SomeText IN ("a","b",Nz(SomeText,""))
0
 
Gustav BrockCIOCommented:
> If I'm searching with "a", what I need is just "a" records (and no null records)
>
> If I'm searching for "b" I need just null records.

You cannot search a field with its own value as the parameter.

WHERE IIF([p]="a", [SomeText]="a", [SomeText] Is Null);

/gustav
0
 
dqmqCommented:
> If I'm searching for "b" I need just null records.

I guess we've moved beyond obscurity of XOR and the nuances of NULL.  Maybe you should take that one up with a marriage counselor. :>)

Here is what you want:

SELECT Fooxor.ID, Fooxor.sometext
FROM Fooxor
GROUP BY Fooxor.ID, Fooxor.sometext
HAVING Fooxor.sometext="A"
OR (Fooxor.sometext Is Null AND Count(Fooxor.sometext)=0);
0
 
dqmqCommented:
I think this works too:

SELECT Fooxor.ID, Fooxor.sometext
FROM Fooxor
GROUP BY Fooxor.ID, Fooxor.sometext
HAVING Fooxor.sometext="A" OR Count(Fooxor.sometext)=0;
0
 
GRayLCommented:
This works:

WHERE nz(sometext,"") = "a" xor nz(sometext,"")=""

0
 
dqmqCommented:
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)

0
 
Gustav BrockCIOCommented:
Not exactly.

#22693127

/gustav
0
 
Gustav BrockCIOCommented:
Sorry, should be:

#a22693127

/gustav
0
 
gothamwwAuthor Commented:
dgmg - yes, that's it - the other solutions were giving me the "a" records and the null records together.  I wanted only "a" records if there was at least one "a" record.  And only Null records if there wasn't. - I'll accept yours as the solution, but before I close this question, I'm curious what the last two posts from cactus_data mean - sorry if it's obvious, but I can't decode your codes - what's #a22693127?
0
 
gothamwwAuthor Commented:
sorry - got it, cactus_data: a22693127 is a comment in this thread... I'm afraid I didn't understand it though.
0
 
Gustav BrockCIOCommented:
> If I'm searching with "a", what I need is just "a" records (and no null records)
>
> If I'm searching for "b" I need just null records.

Now:

> I wanted only "a" records if there was at least one "a" record.  
> And only Null records if there wasn't. -

It is not easy when you keep redefining your task.

/gustav
0
 
GRayLCommented:
You looked at:  http:#a22698880  ?
0
 
gothamwwAuthor Commented:
GrayL:

Yes, I did look at #a22698880 - this gave me the "a" records as well as the null records - my guess is this is because it was looking at each record individually.  and at any given time, the value will be either "a" or it will be "null" but it will never be both, so this is effectively an "or".  I this this is what cactus_data meant in: 22690654.

cactus_data:

You are right I should be more careful with my language, but those two examples you quoted in 22715553 are requesting the same solution to the same problem - nothing's been redefined - just in one example I'm using "a" and in another I'm using "b" - but the basic question remains unchanged.  I'll try to be more careful in the future.  Thanks to all for your help.
0
 
GRayLCommented:
Doesn't my WHERE clause behave as you requested in your qestion?
0
 
GRayLCommented:
question - sorry.
0
 
gothamwwAuthor Commented:
GRayL:

When I tested it, your where clause returned records 1, 2, 3 and 4.  (all records matching "a" and all Null records as well).  I wanted just records 1 and 2 (those matching "a") but NOT 3 and 4 (those matching Null).
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 13
  • 9
  • 6
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now