Solved

need help with xor

Posted on 2008-10-10
36
1,156 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
  • 13
  • 9
  • 6
  • +3
36 Comments
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 100 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
 
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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 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 49

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 49

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 300 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 49

Expert Comment

by:Gustav Brock
ID: 22706221
Not exactly.

#22693127

/gustav
0
 
LVL 49

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 49

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now