Access 2016 Action Queries behaving strangely

Biggles1
Biggles1 used Ask the Experts™
on
I have a table which includes a "Yes/No" field.  See attached.
Some of the records have their "Yes/No" Set to Yes.  See attached
I created a query that includes only :"Yes/No" set to "Yes"
The result is a dataset where ALL the "Yes/No" Fields are set to "Yes"

Has anyone encountered this phenomenon?
Table-with-Checkmarks.JPGRecordsAttributeSelected.JPGQueryResults.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
-1 is True
0 is False
Looks like you have a select statement that correctly selects all the records where the value is true. What would you expect to be different?
Biggles1Founder/CEO

Author

Commented:
The problem is that if you look at the table you will see that NOT all the values are true!  THAT's the problem
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Biggles1Founder/CEO

Author

Commented:
-1 is True
0 is False

I knew that.  It seems ACCESS does not know.  Because even through only SOME records are "-1" the query returns ALL the records even those that are NOT -1.
But its not the same records?
In the first screenshot, 4 people have the value true. They are the first four records in the last screentshot. The ones with 0 in them are not found in the last screenshot/recordset.
ste5anSenior Developer

Commented:
The problem is that if you look at the table you will see that NOT all the values are true!  THAT's the problem
Nope, all values in your filtered query are -1, thus equal to True or Yes:

Capture.PNG
p.s. in numbers
False => 0
True => NOT False
That means that any value not equal to the value of 0 (or 0.000) is interpreted as True.
Software & Systems Engineer
Commented:
Lets take it from the start..probably the confusing part is the 1st screenshot that implies that this is the normal situation and then you apply the filtering.
If the 1st screen is the result of the filter 1st thing to do is to check that the field MailRecipient is actually a Boolean...then take one of 0s and just try to figure out why is represented as "true"
Biggles1Founder/CEO

Author

Commented:
Thanks guys!  Quick response when I was in a real bind timewise!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial