query yes/no field

[Suzie]

Hi - I am trying to create a query to show only fields that have "yes" as the
answer to a yes/no question on a table. I want to use this query to print a
report listing all the items. I realise I will have to insert something in
the criteria section on the query but I am not sure where to find help with
creating criteria.

Can you help please?

 

[KARL DEWEY]

Yes is stored as a -1 (minus one) and No as 0 (zero).
Total the -1's and change to positive number like this --
Abs(Sum([YourField]))
Or
(Sum([YourField])) * -1

 

[fredg]

I forgot to say, I have put a parameter asking for the container number when
you run the query (that seems to work) and I have tried putting "If"="yes" in
the criteria line but this doesn't seem to exclude no answers when you run
the query.

To return only records in which the Yes/No field is Yes, simply write
-1
on the query grid's criteria line of that check box column.
If you have additional query criteria, make sure you write the -1 on
the same row as the other column's criteria for an "AND" where clause:
Where [Company] = "XYZ Co." AND CheckBox = -1
If you write the -1 on the next row you will create an "OR" criteria:
Where [Company] = "XYZ Co." OR CheckBox = -1

 

[Suzie]

Hi Fred

I tried putting -1 and also =-1 but I still get the full list of items not
only the "yes" items. Any idea where I might be going wrong?

This is the only criteria in the query.
--
Suzie

I forgot to say, I have put a parameter asking for the container number when
you run the query (that seems to work) and I have tried putting "If"="yes" in
the criteria line but this doesn't seem to exclude no answers when you run
the query.

To return only records in which the Yes/No field is Yes, simply write
-1
on the query grid's criteria line of that check box column.
If you have additional query criteria, make sure you write the -1 on
the same row as the other column's criteria for an "AND" where clause:
Where [Company] = "XYZ Co." AND CheckBox = -1
If you write the -1 on the next row you will create an "OR" criteria:
Where [Company] = "XYZ Co." OR CheckBox = -1

 

[Suzie]

Hi again, I think I have worked out what seems to be happening, if I put the
-1 in the criteria for one field only it shows all the fields of the
container record if it has "yes" in that particular field, but, if I put the
-1 in all the fields and any of the fields are "NO" it does not show any
fields from that container record.

I am thinking of giving up!!
--
Suzie

Hi Fred

I tried putting -1 and also =-1 but I still get the full list of items not
only the "yes" items. Any idea where I might be going wrong?

This is the only criteria in the query.
--
Suzie

I forgot to say, I have put a parameter asking for the container number when
you run the query (that seems to work) and I have tried putting "If"="yes" in
the criteria line but this doesn't seem to exclude no answers when you run
the query.

To return only records in which the Yes/No field is Yes, simply write
-1
on the query grid's criteria line of that check box column.
If you have additional query criteria, make sure you write the -1 on
the same row as the other column's criteria for an "AND" where clause:
Where [Company] = "XYZ Co." AND CheckBox = -1
If you write the -1 on the next row you will create an "OR" criteria:
Where [Company] = "XYZ Co." OR CheckBox = -1

 

[John Spencer (MVP)]

Try putting the -1 on different criteria lines so that you will get matches if
any of the fields is yes.

IF you put the -1 on the same line that means you are "and-ing" the criteria
while if you put the -1 on separate lines you are "or-ing" the criteria.

And means every field must match the criteria
Or means that at least one field must match the criteria.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Suzie]

Thanks for your help, I'm sure I'll be back with more queries over the next
few weeks as I get this thing up and running.