Can't query properly on yes/no field

[Gina Maylone]

I am simply trying to query Posted=false. Have tried posted=0 and posted <>true and posted <>-1. What I'm trying to show is: in a subform on the Inventory form I want to show any products that are still open on a Purchase Order, linked by product number. The sql behind the subform

SELECT DISTINCT PODetails.DetailID, PODetails.PO, InventoryReceiving.QtyReceived, PODetails.Productnumber, PODetails.QTYOrdered, PODetails.ETA, PODetails.DateReceived, PODetails.RecYN, PODetails.Description, InventoryTransactions.Posted
FROM (PODetails LEFT JOIN InventoryReceiving ON PODetails.PO = InventoryReceiving.PurchaseOrder) LEFT JOIN InventoryTransactions ON PODetails.Productnumber = InventoryTransactions.ProductNumber
WHERE (((InventoryTransactions.Posted)<>-1));

This product should show up on the subform. I have verified the data is correct in all tables. I've tried to filter by form as well, with the same results (nothing).

I feel like I'm missing something really simple. But now I've worked myself into a tizzy.

Thoughts? thanks in advance!

[NoellaG]

Hi,

first leave out the WHERE clause on your SQL. If still no records show there must be something wrong in your joins. If the record shows up: check the field Posted. Is it a Yes/No field or a numeric or even a text field. Does it contain NULL values?

Kind regards
Noëlla

[Gina Maylone]

Thank you Noella! It is a yes/no field. If I use no criteria (where) I get all of the records as expected. When viewing the results, I can filter for selected or cleared. there are no null values that I know of, just selected or cleared (yes or no)

[NoellaG]

I see you show the lines in a subform, can you check the link master/child field of the subform object?

[NoellaG]

or try
WHERE not(

InventoryTransactions.Posted)

[Gina Maylone]

Ohhhh, I thought you were onto something clever with that where statement, but sadly, it didn't produce the results I need. My subform and main form are linked by product number...I do think my joins have something to do with it....I just haven't been able to untangle them this morning. Thank you for your help Noella!

[Gina Maylone]

I think because until a product has been checked in as 'received' in Purchase Orders it doesn't get added to the inventory transactions table, and even though I have it setup to include all products on PO, and only those that match from received and transaction tables, its not correct for me to ask it what I am. I think.

[isladogs]

The correct filter clause would just be Posted =False.
I think the issue is you have left joins and the Posted field is on the 'receiving end' of the join arrow.
Also the two inventory tables don't have a primary key field which could be an issue.
Suggest the two ID fields are made primary keys.

As a test, try the same query with two inner joins. Does that work?

[rpeare]

try a criteria of

<>-1 or is null

if you added the field after the fact or it does not have a default value of 0 (false) in the table design you can't get it without checking for null as well

EDIT
your yes/no has 3 distinct states

True
False
Null

For your purposes FALSE and NULL are the same thing

[isladogs]

Unlike in SQL Server where Boolean fields can also be null, boolean fields in Access can only be true or false.
So unless this is a split database with linked sql tables, I disagree with the previous answer.

[rpeare]

the user is using a left join to the table with the yes/no field, if he/she wants the record to appear where there is no value he/she must include a check for null.

Example

T_ID	TestYesNo
1	No
2	Yes

TID
1
2
3

Query

SELECT Table2.TID, Table1.TestYesNoFROM Table2 LEFT JOIN Table1 ON Table2.TID = Table1.T_ID
WHERE (((Table1.TestYesNo)<>-1));

will produce 1 record, record 1

Query

SELECT Table2.TID, Table1.TestYesNo
FROM Table2 LEFT JOIN Table1 ON Table2.TID = Table1.T_ID
WHERE (((Table1.TestYesNo)<>-1 Or (Table1.TestYesNo) Is Null));

will produce 2 records, 1 and 3

[GinaWhipp]

Hmm, actually you can have NULL in an Access query if you have records on the one side that do not have records on the many said and not using an INNER join, real pain. Good news is they shouldn't show hen filtering on Yes\No. On SQL Server, BIT fields need a default value or you won't be able to update the table. I said all that to say you need to flip between True\False Or Is NULL

[isladogs]

I accept the reasoning in the last two posts but my comment regarding boolean fields in Access was correct.

As an aside, false is always zero. True is any value other than zero but Access will change it to -1.
That's why I said to use Posted=False though Posted=0 works just as well

[CJ_London]

@Colin

Unless I'm missing the point, boolean fields can be null in access - just don't provide a default value of true or false. You can also set a triple state with a checkbox or option control on a form or report (but not in table design) where the third state is null.

my two penneth for the criteria would be

WHERE nz(Posted,false)=false

or

WHERE not nz(Posted,false)

[isladogs]

@Colin

Unless I'm missing the point, boolean fields can be null in access - just don't provide a default value of true or false. You can also set a triple state with a checkbox or option control on a form or report (but not in table design) where the third state is null.

Hi Ajax
Yes I think you may be missing the point
Any boolean fields left blank in Access are FALSE
See https://sourcedaddy.com/ms-access/boolean-data.html

You can indeed have a triple state checkbox which is NULL (grey shaded) but ONLY for unbound controls.
If you try doing that for bound controls it doesn't work

In SQL Server, and many other database applications, boolean fields can be null unless you specify otherwise.
In such cases, if no default value is set, when the sql server table is linked to Access issues arise when an update query is run on any records with null booleans
Access is unable to handle the nulls and throws a write conflict error

For example, see https://www.access-programmers.co.uk...t+boolean+null and https://www.access-programmers.co.uk...t+boolean+null