Solved SQL problem

[isladogs]

AFAIK, a Yes/No field cannot be Null. It is either True or False (-1 or 0). However, since Null is not -1 it would be treated as False.

Sorry but not quite correct
An unbound Boolean control can be null and appears 'filled' (depending on version)
For Boolean fields, any value not equal to 0 is treated as True. This is important for e.g. linked SQL Server tables where True = 1

CBool(Null) gives error 94 - invalid use of Null

The item I would quibble with from post #17 was the use of the word 'dyadic'

 

[June7]

A VBA expression where 2=True would return False. Null=True returns Null and in an IIf() that would evaluate to the False condition.
?-1=True
True
?2=True
False
?Null=True
Null
?IIf(Null=True,"A","B")
B

CBool() forces number input to be treated as True or False, not that the input is True. Errors on non-numeric input.

Still, a Yes/No field is either True or False, never Null. Yes, an UNBOUND textbox can have triple state set to allow Null and if that were used as criteria in query no records return: SELECT * FROM table WHERE Active=Null;
Also, no records return for: SELECT * FROM table WHERE Active=2;
So, I have to correct my earlier statement. Only True and False (-1/0) are recognized by Access Yes/No field. Even though Active=2 is False, query does not return records where Active is False, as with Null, just cannot evaluate. Wrapping input in CBool() could handle the 2 input but not Null (secondary wrap with Nz?).

Access sees linked SQLServer Boolean field with -1 and 0 values. Type input as anything but -1 or 0 and the field will show True (-1). Try Delete and get error as cannot be Null, at least not in my linked field.
I suppose pass-through query would have to use 1 for True.

 

[isladogs]

CBool() forces number input to be treated as True or False

No. Like any conversion function, CBool is converting the supplied input expression which can be any valid string or numeric expression.

?CBool(False)
False
?CBool(True)
True
?CBool("False")
False
?CBool("True")
True

Also for completeness, as null cannot equal anything, not even another null

?Null=False
Null

?Null=Null
Null

 

[CJ_London]

The ‘Boolean’ value in the query may produce a null - all depends how it is determined. For example

Istrue: iif(a=b, true)

Will return a null if false

 

[isladogs]

True but of course Istrue: iif(a=b, true, false) gives false when a<>b

 

[June7]

?CBool("A") triggers error
So CBool() understands text "True" should be converted to Boolean True.

 

[John Sh]

@John Sh
You haven't stated which Access version you are using or whether you are using the Monaco SQL editor
Also you haven't said whether an error message appears when the update fails.
These may all be relevant to your issue

I'm running Access 2016 and currently running an update.
I am writing the sql in the vba editor.
OK. I've run this after the update and it seems to be working properly now. Apparently some sort of bug in my setup.
My apologies for wasting your time and my thanks to all for your efforts.
You are much appreciated.
John

 

[The_Doc_Man]

The item I would quibble with from post #17 was the use of the word 'dyadic'

Colin, from the VBA Language Spec, 2014 version, there is this excerpt:

The table seems to indicate that an Inclusive OR of the form "left OR right" - a dyadic operation - will tolerate one null (but not two).

 

[CJ_London]

True but of course Istrue: iif(a=b, true, false) gives false when a<>b

Agreed - but my point is we don’t know how the OP has determined these values, so they could have introduced a null value

Either way, it appears the problem has been resolved

 

[isladogs]

The table seems to indicate that an Inclusive OR of the form "left OR right" - a dyadic operation - will tolerate one null (but not two).

The details in the VBA Language Spec, 2014 version are useful as background reference (thank you)
See also this MS Learn article from Feb 2025: https://learn.microsoft.com/en-us/o.../ms-vbal/d5418146-0bd2-45eb-9c7a-fd9502722c74

However, as far as Boolean fields in Access are concerned, the null lines are largely irrelevant as discussed above.

Either way, it appears the problem has been resolved

Agreed. As is often the case, the level of response went into far more detail than was strictly required from the initial question.