SELECT: A is true, B is true, but A AND B is false

[groleau]

Why does Access have to rewrite our SQL
so it looks like LISP ?

Anyway, I have tried three things:

A - WHERE Adj_Reason = '50' -- (it's a text field)
B - WHERE                       Service Like '*GA*'
C - WHERE Adj_Reason = '50' AND Service Like '*GA*'

A and B each show lots of records. There are only
a few rows that are in only one of the sets.
But "C" selects no rows.

I tried creating them directly in SQL and with the GUI.
Same results.

--
Wes Groleau

http://www.Parkview.com/

 

[lespaul]

just because A and B return results doesn't mean that C will as well. Are you sure you have records that meet BOTH conditions?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[groleau]

Oops, I forgot to mention it.

Yes, each A and B displays more than a screen full of records, and 90% of them are common.

--
Wes Groleau

http://www.Parkview.com/

 

[cmmrfrds]

Can you paste in all your SQL.

 

[groleau]

OOPS, due to cut and paste error, I was using Svc_Charge when I SHOULD have been using Service. Svc_Charge is a currency field.

Which changes the question to

How does

Like '*GA*'

find a hundred matches in a Currency field, but none when ANDed with another condition?

Oh, well, at least I have it working now.

--
Wes Groleau

http://www.Parkview.com/

 

[lespaul]

So when you run the 50 query criteria you see records in that result set that have GA in the Service field, but those records don't show up when you use both criteria?

Adj_Reason = '50'
Field Field Adj_Reason Service
blay blay 50 Something
blay blay 50 aGAin <- and this record doesn't show when using both criteria?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[groleau]

Yes, lespaul -- but it seems your post and my update crossed in the mail.

Due to my error, the second crtieria should NOT match,
making the mystery why it did when standing alone.

Could be there was another error that went away during the investigation. But no way to find that out now.

--
Wes Groleau

http://www.Parkview.com/

 

[MichaelRed]

CHECK CAREFULLY. there is a surfit of advice aginst using wild cards as the first character in a (search) criteria clause. such constructs are reliably un-reliable. expecting the cartesian join of such with additional logic to be have any reliablility is unreliable?




MichaelRed