Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

need help with xor

Posted on 2008-10-10
36
Medium Priority
?
1,227 Views
Last Modified: 2013-11-29
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
Comment
Question by:gothamww
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 9
  • 6
  • +3
36 Comments
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 400 total points
ID: 22689887

  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
 
LVL 44

Expert Comment

by:GRayL
ID: 22689926
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
 
LVL 42

Expert Comment

by:dqmq
ID: 22689949
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 44

Expert Comment

by:GRayL
ID: 22689957
It depends how you wrote the query.  Can you post the SQL here?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22690071
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
 
LVL 42

Expert Comment

by:dqmq
ID: 22690076
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22690107
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
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 400 total points
ID: 22690654
> 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
 

Author Comment

by:gothamww
ID: 22691083
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 22691185

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

/gustav
0
 

Author Comment

by:gothamww
ID: 22691785
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22691949
WHERE Isnull(fooXor.SomeText)  -   will return the null records
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22691997
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22692073
Hi Jeff:  I gave it - I think.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22692080
Seen the light!

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

Author Comment

by:gothamww
ID: 22692086
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
 

Author Comment

by:gothamww
ID: 22692093
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
 

Author Comment

by:gothamww
ID: 22692142
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22692343
Maybe this:

WHERE SomeText IN ("a","b",Nz(SomeText,""))
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22692353
Maybe this:

WHERE SomeText IN ("a","b",Nz(SomeText,""))
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22692357
Maybe this:

WHERE SomeText IN ("a","b",Nz(SomeText,""))
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 22693127
> 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
 
LVL 42

Expert Comment

by:dqmq
ID: 22694294
> 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
 
LVL 42

Expert Comment

by:dqmq
ID: 22698485
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22698880
This works:

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

0
 
LVL 42

Accepted Solution

by:
dqmq earned 1200 total points
ID: 22705585
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 22706221
Not exactly.

#22693127

/gustav
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 22706231
Sorry, should be:

#a22693127

/gustav
0
 

Author Comment

by:gothamww
ID: 22714485
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
 

Author Comment

by:gothamww
ID: 22714635
sorry - got it, cactus_data: a22693127 is a comment in this thread... I'm afraid I didn't understand it though.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 22715553
> 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
 
LVL 44

Expert Comment

by:GRayL
ID: 22717319
You looked at:  http:#a22698880  ?
0
 

Author Comment

by:gothamww
ID: 22721685
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
 
LVL 44

Expert Comment

by:GRayL
ID: 22723206
Doesn't my WHERE clause behave as you requested in your qestion?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22723210
question - sorry.
0
 

Author Comment

by:gothamww
ID: 22723485
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question