Exclude if a check box is checked otherwise choose everythin

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to exclude all entries containing CA if a check box is checked on a form, otherwise I would like to retrieve all of the records. I tried the following and it works for excluding CA when the box is checked, but when the box isn't checked it says No records available. Please advise

Not Like IIf([Forms]![frmReports].[chkExclude]=-1,"CA")
 
Hi,


... WHERE Forms!FormName!CheckBoxName IMP FieldName <> 'CA'


where IMP, the imply operator, already predefined, is with the following
resolution table

IMP | b= TRUE FALSE NULL
-------------------+---------------------------------------------------
a=TRUE | TRUE FALSE NULL
FALSE | TRUE TRUE TRUE
NULL | TRUE NULL NULL


Since the check box can only be true or false, we only examine the first two
lines. If the check box is false, the criteria is always true, all the
records are returned. If the check box is true, only the records where
FieldName <> 'CA' are returned. That is what the spec. were asking.



Hoping it may help,
Vanderghast, Access MVP


SHIPP said:
I would like to exclude all entries containing CA if a check box is
Click to expand...
checked on a form, otherwise I would like to retrieve all of the records. I
tried the following and it works for excluding CA when the box is checked,
but when the box isn't checked it says No records available. Please advise.
Not Like IIf([Forms]![frmReports].[chkExclude]=-1,"CA")
Click to expand...
 
Hi Michel,

Nice.

One possibly trivial addition might be to wrap in NZ?

NZ(Forms!FormName!CheckBoxName IMP FieldName <> 'CA',-1)

or

(Forms!FormName!CheckBoxName IMP FieldName <> 'CA') OR
(FieldName Is Null)


to for sure return records where checkbox is TRUE
and fieldname is NULL (so condition returns NULL).

Of course if the field will never be NULL, this concern
is irrelevant and nitpicky.

Don't mean to nitpick....

Michel Walsh said:
Hi,


... WHERE Forms!FormName!CheckBoxName IMP FieldName <> 'CA'


where IMP, the imply operator, already predefined, is with the following
resolution table

IMP | b= TRUE FALSE NULL
-------------------+---------------------------------------------------
a=TRUE | TRUE FALSE NULL
FALSE | TRUE TRUE TRUE
NULL | TRUE NULL NULL


Since the check box can only be true or false, we only examine the first two
lines. If the check box is false, the criteria is always true, all the
records are returned. If the check box is true, only the records where
FieldName <> 'CA' are returned. That is what the spec. were asking.



Hoping it may help,
Vanderghast, Access MVP


SHIPP said:
I would like to exclude all entries containing CA if a check box is
Click to expand...
checked on a form, otherwise I would like to retrieve all of the records. I
tried the following and it works for excluding CA when the box is checked,
but when the box isn't checked it says No records available. Please advise.
Not Like IIf([Forms]![frmReports].[chkExclude]=-1,"CA")
Click to expand...
Click to expand...
 
Back
Top