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.

 

[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