All or Nothing..

[Guest]

I must filter out in my query all Clients who have animals or return all
Clients depending on whether the Visitors form shows either Yes or No in the
fear of animals YES/NO combo box.
In a query based on the "Clients" Table, "CientsAnimals" field, (which
contains values like Cat, Dog, None, Horse, Parot, etc) I have written an IIF
test in the criteria but don't know what to put as the false response to
return All records Including "None" if the visitor says "No" to fear of
animals.
What replaces the ?????? in my IIF test?

IIF(Forms![Visitors]![FearOfAnimals],"None",?????)

Many Thanks in advance for any help.
Andy..

 

[Rick Brandt]

I must filter out in my query all Clients who have animals or return
all Clients depending on whether the Visitors form shows either Yes
or No in the fear of animals YES/NO combo box.
In a query based on the "Clients" Table, "CientsAnimals" field, (which
contains values like Cat, Dog, None, Horse, Parot, etc) I have
written an IIF test in the criteria but don't know what to put as the
false response to return All records Including "None" if the visitor
says "No" to fear of animals.
What replaces the ?????? in my IIF test?

IIF(Forms![Visitors]![FearOfAnimals],"None",?????)

Many Thanks in advance for any help.
Andy..

Add a calculated control to the query...

HasAnimals: ClientsAnimals <> "None"

That will return True for those who have animals and False for those who don't.
Use criteria on that field of...

=Forms![Visitors]![FearOfAnimals] or Forms![Visitors]![FearOfAnimals] = False

The true/false logic might need to be reversed as it's not clear to me what your
Yes/No field is indicating.

 

[Guest]

Hi Rick
The YES/NO field on the visitors form indicates whether the visitor has a
fear of animals and must only visit Clients with "None" in the
"ClientsAnimals" Field other wise they can visit All clients whether they
have animals or not. i.e
All Clients records returned by query for NO response.
or Only records with "None" for a YES response.
Andy..
*************

I must filter out in my query all Clients who have animals or return
all Clients depending on whether the Visitors form shows either Yes
or No in the fear of animals YES/NO combo box.
In a query based on the "Clients" Table, "CientsAnimals" field, (which
contains values like Cat, Dog, None, Horse, Parot, etc) I have
written an IIF test in the criteria but don't know what to put as the
false response to return All records Including "None" if the visitor
says "No" to fear of animals.
What replaces the ?????? in my IIF test?

IIF(Forms![Visitors]![FearOfAnimals],"None",?????)

Many Thanks in advance for any help.
Andy..

Add a calculated control to the query...

HasAnimals: ClientsAnimals <> "None"

That will return True for those who have animals and False for those who don't.
Use criteria on that field of...

=Forms![Visitors]![FearOfAnimals] or Forms![Visitors]![FearOfAnimals] = False

The true/false logic might need to be reversed as it's not clear to me what your
Yes/No field is indicating.

 

[Rick Brandt]

Hi Rick
The YES/NO field on the visitors form indicates whether the visitor
has a fear of animals and must only visit Clients with "None" in the
"ClientsAnimals" Field other wise they can visit All clients whether
they have animals or not. i.e
All Clients records returned by query for NO response.
or Only records with "None" for a YES response.
Andy..

In that case...

HasNoAnimals: ClientsAnimals = "None"

=Forms![Visitors]![FearOfAnimals] or Forms![Visitors]![FearOfAnimals] = False

 

[Guest]

Thank you Rick but:
Your solution doesn't answer the problem, let me explain.
Your solution gives
Criteria..-1 result ..A
Criteria...0 result ..B
What I need is the third way!
Criteria..-1 result ..A (only records with 'none' in the Animals field)
Criteria...0 result ..A as well as B (all records, with or without animals)
Does that make sense?
Andy..
******************

Hi Rick
The YES/NO field on the visitors form indicates whether the visitor
has a fear of animals and must only visit Clients with "None" in the
"ClientsAnimals" Field other wise they can visit All clients whether
they have animals or not. i.e
All Clients records returned by query for NO response.
or Only records with "None" for a YES response.
Andy..

In that case...

HasNoAnimals: ClientsAnimals = "None"

=Forms![Visitors]![FearOfAnimals] or Forms![Visitors]![FearOfAnimals] = False

 

[Rick Brandt]

Thank you Rick but:
Your solution doesn't answer the problem, let me explain.
Your solution gives
Criteria..-1 result ..A
Criteria...0 result ..B
What I need is the third way!
Criteria..-1 result ..A (only records with 'none' in the Animals
field) Criteria...0 result ..A as well as B (all records, with or
without animals) Does that make sense?
Andy..

Did you try it?

The second half of the criteria (on the right side of the OR) in the query says
return all records where the CheckBox on the form = False. When the CheckBox is
False that part of the criteria willl be satisifed for ALL records so you will
get ALL records.

To clarify...the criteria is saying: when the control on the form is true return
the rows where this field is true and when the control on the form is false
return all rows.

 

[Guest]

My Appologies Rick
Your solution wors perfectly.
I miss read it, thinking the part after the OR was a different solution to
acheive a different result and so only tried it with the first half. (Stupid
Boy!!!)
Many thanks for your assistance and if you respond to any further queries I
put forward, I promise to pay attention to the master.
Best Wishes for the New Year.
Andy..
***********************