Filtering a Yes/No field

[Guest]

In my SQL statement how would I only return the value of YES from a yes/no
field?

 

[Guest]

try
Where FieldName = True

 

[Guest]

That didn't work. I put the string "Where FieldName = True" the Paid colum
and it's still returning everything. As it stands now I only have two Yes's
so it should only be returning just the two.

 

[Rick Brandt]

In my SQL statement how would I only return the value of YES from a
yes/no field?

I find that the most reliable way to apply criteria to Yes/No fields is to
use..

= 0
(for false/no)

....and...

<> 0
(for true/yes)

This is especially true if you ever move your data to SQL Server because
while Access/Jet uses negative one for True, SQL Server bit fields use
positive one for True. By testing for zero all the time your queries would
not need to be redesigned.

 

[fredg]

In my SQL statement how would I only return the value of YES from a yes/no
field?

for Yes.
Where TableName.[YesNo field] = -1

for No.
Where TableName.[YesNo field] = 0

 

[Guest]

Anothe option will be

Where FieldName = Yes

though, the True should have worked

 

[fredg]

That didn't work. I put the string "Where FieldName = True" the Paid colum
and it's still returning everything. As it stands now I only have two Yes's
so it should only be returning just the two.

You asked regarding your SQL statement. However, you appear to have
written that statement in the query QBE grid. That is not where it
goes.

If you write
True
as the criteria of the YesNo field grid, then look at the SQL it will
have written
Where YourTable.[YesNoField] = True

Note: True is a number value, not a string value. Do NOT surround True
with any quote marks.
I would also suggest using -1 instead of True. It is less ambiguous
than True or Yes, both of which equal -1.

 

[Guest]

Thanks for everyones help. I now have it working thanks to you guys.

In my SQL statement how would I only return the value of YES from a yes/no
field?

for Yes.
Where TableName.[YesNo field] = -1

for No.
Where TableName.[YesNo field] = 0