Left Join returning a boolean field causing issues with "True/False/Null"

[InstructionWhich7142]

I've been troubleshooting an issue where a totals query based on the above left join query (with the left join that returns the boolean field that may not exist so can be NULL) gives 1 result and if I instead have it make a table and run the same test I get a different result,

The outcome was that "NOT [field1]" only returns values from the LEFT JOINed table that exist and are false, the values that didn't exist, i.e. that were NULL, don't match that criteria, however the inverse "true" test works, because only records that exist can be true

Apart from NZ is there some other way in logic to get the result I Want? I've tried: NOT field1=TRUE but that must also end up being processed to "NOT NULL" ?

(typically this is buried in a mess of totals queries so trying to post examples is hard, also it's 2 fields, not 1, a couple of NZ's fix it but I'd like to know other workarounds)

Also it's super hard to spot because in the data view NULL looks identical to FALSE on boolean fields

 

[The_Doc_Man]

The NZ function was designed for exactly this purpose. It is relatively quick and efficient, and it works in the SQL context.

The only other situation that I can think of is where you build the totals query based on a WHERE clause containing a variant of the IS NULL syntax to eliminate the individually NULL records.

Learn the differences between Is Null and IsNull() in Access | TechRepublic

How many null values exist? What is the difference between a null value and a blank entry? This article answers those questions and more by discussing what to look for when creating Access apps that return a null value and cause errors to boot.

www.techrepublic.com

 

[June7]

Might find this of interest http://allenbrowne.com/NoYesNo.html

 

[InstructionWhich7142]

I'd tried all sorts of fudge like "NOT [field1] = true" but it wasn't until I was falling asleep that it occured to me "[field1] <> True" will catch null and false, doh!

 

[ebs17]

"[field1] <> True" will catch null and false

No.

Modern SQL: Three-Valued Logic (3VL) — Purpose, Benefits and Special Cases

Making decision despite missing SQL data (NULL): How to avoid OR…IS NULL in the WHERE clause.

modern-sql.com

? False <> True, NULL <> True
Wahr          Null

' but
WHERE YourField = False OR YourField IS NULL

' worse because no index usage
WHERE Nz(YourField, False) = False
WHERE IIf(YourField Is NULL, False, YourField) = False

Nz as a function of the access object is not part of Jet-SQL and can therefore only be used via the Expression Service.

 

[Pat Hartman]

The outcome was that "NOT [field1]" only returns values from the LEFT JOINed table that exist and are false, the values that didn't exist, i.e. that were NULL, don't match that criteria, however the inverse "true" test works, because only records that exist can be true

Null is not true and it is not false. It is also not equal to anything not even itself. True is also not "not equal" to Null. Comparing any value to Null always returns Null. Never True nor False. Therefore, when you have null as a possible variable, you must test for it explicitly OR only look for the true value OR convert nulls to a non-null value such as "" or False as appropriate.

@ebs17 gave you the solution I would probably use if I were looking for the False values. Although booleans are rarely useful as indexes unless they are part of a multi-field index so I would also consider Doc's solution. That syntax, using negative logic is:

Where Nz(SomeField, False) <> True
or preferably
Where Nz(SomeField,False) = False
or if you think the database engine will use the index
Where SomeField = False or SomeField Is Null '' would be the most efficient variant

The Nz() converts the null value to False. People always have less trouble "processing" positive tests than negative tests. We won't even talk about the silly double negatives that I've seen people post.

 

[InstructionWhich7142]

OR only look for the true value

This is what I was trying to explain was my elegant solution, not that NZ isn't elegant but I was annoyed and wanted to solve it a different way

 

[Pat Hartman]

Efficient is far better than elegant. Those are the solutions worth pursuing. As ebs17 pointed out

Where SomeField = False or SomeField Is Null

Is the most efficient since it may use an index if one is available whereas the NZ() will not.