True, False, or Both criteria

[BruceM]

I have been trying to set the criteria for a Yes/No field so that the user
can choose to see True, False, or both. The "both" part has me stumped. I
have tried variants of:

IIf([Yes or No or Both] = "Yes",True, _
IIf([Yes or No or Both] = "No",False, _
True or False))

Of course, the True or False does not work, since it seems to evaluate to
True. However, True or False as the only criteria gives me all of the
records.

In practice, for a report I would probably use an unbound form to select the
criteria, then build the record source at run time with no WHERE condition
for the Yes/No field if the user chooses to see both True and False. For a
form I would use a similar approach, except I would probably have the
criteria selections on the form itself rather than just when the form is
opened.

However, my question here is because I am trying to get to the root of the
logic behind the failure of the Both criterium.

 

[Allen Browne]

Bruce, this is untested, but try a SQL statement like this:
SELECT * FROM Table1
WHERE IIf([Yes or No or Both] Like "Y*", [MyYesNo] = True,
IIf([Yes or No or Both] Like "N*", [MyYesNo] = False, True))

The idea is that a WHERE clause is ultimately something that evaluates to
True or False (or Null) for each record. So:

- If the user enters something that starts with Y, you compare your yes/no
field to True, and only the records where the field is true are selected.

- If the user enters something that starts with N, you compare the yes/no
field to False, and only the records where the field is false are selected.

- For all other cases, the expression becomes just:
WHERE True
Since true is always true, it doesn't matter what's in the record: they are
all selected because the expression is always true.

 

[BruceM]

Allen, thanks for the explanation and the sample SQL. It worked as written.
When I switched to design view I saw a single column in which the Field row
contained the expression:

IIf([Yes or No or Both] Like "Y*",[MyYesNo]=True,
IIf([Yes or No or Both] Like "N*",[MyYesNo]=False,True))

The criteria was <>False. The check box in the Show row was unchecked. If
I checked it I got the SQL (I added the alias NewField, which otherwise
showed up as the default Expr1000 in datasheet view):

SELECT IIf([Yes or No or Both] Like "Y*",[MyYesNo]=True,
IIf([Yes or No or Both] Like "N*",[MyYesNo]=False,
True)) AS NewField, *
FROM tblYN
WHERE (((IIf([Yes or No or Both] Like "Y*",[MyYesNo]=True,
IIf([Yes or No or Both] Like "N*",[MyYesNo]=False,
True)))<>False));

NewField shows up in datasheet view as -1 in all cases, including if Y or N
was entered as the criteria.

Unchecking the Show box did not restore the original SQL, but rather left me
with a version like the second one I posted. Such are the perils of
automated code writing.

I sort of understand the logic, now that you have explained it. If the
criteria is Y the expression becomes, in effect:
[MyYesNo] = True
The opposite applies if the criteria is N. If the criteria is neither Y nor
N the expression becomes WHERE True, as you stated. This is the part that
is sinking into my brain slowly, but I think it will settle in eventually.
I thnk I understand that setting WHERE to True is the same as having no
WHERE condition.

Thanks again.

Bruce, this is untested, but try a SQL statement like this:
SELECT * FROM Table1
WHERE IIf([Yes or No or Both] Like "Y*", [MyYesNo] = True,
IIf([Yes or No or Both] Like "N*", [MyYesNo] = False, True))

The idea is that a WHERE clause is ultimately something that evaluates to
True or False (or Null) for each record. So:

- If the user enters something that starts with Y, you compare your yes/no
field to True, and only the records where the field is true are selected.

- If the user enters something that starts with N, you compare the yes/no
field to False, and only the records where the field is false are
selected.

- For all other cases, the expression becomes just:
WHERE True
Since true is always true, it doesn't matter what's in the record: they
are all selected because the expression is always true.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have been trying to set the criteria for a Yes/No field so that the user
can choose to see True, False, or both. The "both" part has me stumped.
I have tried variants of:

IIf([Yes or No or Both] = "Yes",True, _
IIf([Yes or No or Both] = "No",False, _
True or False))

Of course, the True or False does not work, since it seems to evaluate to
True. However, True or False as the only criteria gives me all of the
records.

In practice, for a report I would probably use an unbound form to select
the criteria, then build the record source at run time with no WHERE
condition for the Yes/No field if the user chooses to see both True and
False. For a form I would use a similar approach, except I would
probably have the criteria selections on the form itself rather than just
when the form is opened.

However, my question here is because I am trying to get to the root of
the logic behind the failure of the Both criterium.

 

[Allen Browne]

Yes: the graphical query designer struggles with that kind of thing, but it
still works.

Your final comment indicates you got it.
To eliminate all records from a query, you would write:
SELECT * FROM Table1 WHERE False;
because the False condition is false for all records, so nothing matches.

Similarly, this returns all records:
SELECT * FROM Table1 WHERE True;
because the True condition is true for all records, so everything matches.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for the explanation and the sample SQL. It worked as
written. When I switched to design view I saw a single column in which the
Field row contained the expression:

IIf([Yes or No or Both] Like "Y*",[MyYesNo]=True,
IIf([Yes or No or Both] Like "N*",[MyYesNo]=False,True))

The criteria was <>False. The check box in the Show row was unchecked.
If I checked it I got the SQL (I added the alias NewField, which otherwise
showed up as the default Expr1000 in datasheet view):

SELECT IIf([Yes or No or Both] Like "Y*",[MyYesNo]=True,
IIf([Yes or No or Both] Like "N*",[MyYesNo]=False,
True)) AS NewField, *
FROM tblYN
WHERE (((IIf([Yes or No or Both] Like "Y*",[MyYesNo]=True,
IIf([Yes or No or Both] Like "N*",[MyYesNo]=False,
True)))<>False));

NewField shows up in datasheet view as -1 in all cases, including if Y or
N was entered as the criteria.

Unchecking the Show box did not restore the original SQL, but rather left
me with a version like the second one I posted. Such are the perils of
automated code writing.

I sort of understand the logic, now that you have explained it. If the
criteria is Y the expression becomes, in effect:
[MyYesNo] = True
The opposite applies if the criteria is N. If the criteria is neither Y
nor N the expression becomes WHERE True, as you stated. This is the part
that is sinking into my brain slowly, but I think it will settle in
eventually. I thnk I understand that setting WHERE to True is the same as
having no WHERE condition.

Thanks again.

Bruce, this is untested, but try a SQL statement like this:
SELECT * FROM Table1
WHERE IIf([Yes or No or Both] Like "Y*", [MyYesNo] = True,
IIf([Yes or No or Both] Like "N*", [MyYesNo] = False, True))

The idea is that a WHERE clause is ultimately something that evaluates to
True or False (or Null) for each record. So:

- If the user enters something that starts with Y, you compare your
yes/no field to True, and only the records where the field is true are
selected.

- If the user enters something that starts with N, you compare the yes/no
field to False, and only the records where the field is false are
selected.

- For all other cases, the expression becomes just:
WHERE True
Since true is always true, it doesn't matter what's in the record: they
are all selected because the expression is always true.

I have been trying to set the criteria for a Yes/No field so that the
user can choose to see True, False, or both. The "both" part has me
stumped. I have tried variants of:

IIf([Yes or No or Both] = "Yes",True, _
IIf([Yes or No or Both] = "No",False, _
True or False))

Of course, the True or False does not work, since it seems to evaluate
to True. However, True or False as the only criteria gives me all of
the records.

In practice, for a report I would probably use an unbound form to select
the criteria, then build the record source at run time with no WHERE
condition for the Yes/No field if the user chooses to see both True and
False. For a form I would use a similar approach, except I would
probably have the criteria selections on the form itself rather than
just when the form is opened.

However, my question here is because I am trying to get to the root of
the logic behind the failure of the Both criterium.

 

[BruceM]

Now if only I can get this to stay in my head until I need it again.

Yes: the graphical query designer struggles with that kind of thing, but
it still works.

Your final comment indicates you got it.
To eliminate all records from a query, you would write:
SELECT * FROM Table1 WHERE False;
because the False condition is false for all records, so nothing matches.

Similarly, this returns all records:
SELECT * FROM Table1 WHERE True;
because the True condition is true for all records, so everything matches.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, thanks for the explanation and the sample SQL. It worked as
written. When I switched to design view I saw a single column in which
the Field row contained the expression:

IIf([Yes or No or Both] Like "Y*",[MyYesNo]=True,
IIf([Yes or No or Both] Like "N*",[MyYesNo]=False,True))

The criteria was <>False. The check box in the Show row was unchecked.
If I checked it I got the SQL (I added the alias NewField, which
otherwise showed up as the default Expr1000 in datasheet view):

SELECT IIf([Yes or No or Both] Like "Y*",[MyYesNo]=True,
IIf([Yes or No or Both] Like "N*",[MyYesNo]=False,
True)) AS NewField, *
FROM tblYN
WHERE (((IIf([Yes or No or Both] Like "Y*",[MyYesNo]=True,
IIf([Yes or No or Both] Like "N*",[MyYesNo]=False,
True)))<>False));

NewField shows up in datasheet view as -1 in all cases, including if Y or
N was entered as the criteria.

Unchecking the Show box did not restore the original SQL, but rather left
me with a version like the second one I posted. Such are the perils of
automated code writing.

I sort of understand the logic, now that you have explained it. If the
criteria is Y the expression becomes, in effect:
[MyYesNo] = True
The opposite applies if the criteria is N. If the criteria is neither Y
nor N the expression becomes WHERE True, as you stated. This is the part
that is sinking into my brain slowly, but I think it will settle in
eventually. I thnk I understand that setting WHERE to True is the same as
having no WHERE condition.

Thanks again.

Bruce, this is untested, but try a SQL statement like this:
SELECT * FROM Table1
WHERE IIf([Yes or No or Both] Like "Y*", [MyYesNo] = True,
IIf([Yes or No or Both] Like "N*", [MyYesNo] = False, True))

The idea is that a WHERE clause is ultimately something that evaluates
to True or False (or Null) for each record. So:

- If the user enters something that starts with Y, you compare your
yes/no field to True, and only the records where the field is true are
selected.

- If the user enters something that starts with N, you compare the
yes/no field to False, and only the records where the field is false are
selected.

- For all other cases, the expression becomes just:
WHERE True
Since true is always true, it doesn't matter what's in the record: they
are all selected because the expression is always true.

I have been trying to set the criteria for a Yes/No field so that the
user can choose to see True, False, or both. The "both" part has me
stumped. I have tried variants of:

IIf([Yes or No or Both] = "Yes",True, _
IIf([Yes or No or Both] = "No",False, _
True or False))

Of course, the True or False does not work, since it seems to evaluate
to True. However, True or False as the only criteria gives me all of
the records.

In practice, for a report I would probably use an unbound form to
select the criteria, then build the record source at run time with no
WHERE condition for the Yes/No field if the user chooses to see both
True and False. For a form I would use a similar approach, except I
would probably have the criteria selections on the form itself rather
than just when the form is opened.

However, my question here is because I am trying to get to the root of
the logic behind the failure of the Both criterium.