Newbie question - Querying Yes/No fields using form

  • Thread starter Thread starter Hulo
  • Start date Start date
H

Hulo

I have a table with a two state boolean field named "nophoto". Now I
want to create a query which will show all the results (both true and
false), true results only and false results only.

I am using a form named "frmSearch" where there is a text box, also
named "nophoto" to supply data to the query.

1. If the criteria in the query is given as -

[Forms]![frmSearch]![nophoto]

I can supply only the values 1 and 0 from the text box, thereby getting
only the records with either true or false values but not both.
Supplying * does not seem to work, as it does not return any record.

2. So I changed the criteria to -

Like [Forms]![frmSearch]![nophoto]

Now, * and 0 works and returns all records and records with false
values. Strangely when the value of 1 is supplied, no records are
returned, although there are many true values.

How to solve this problem? Why is 0 working but 1 not working in the
later case? Is there any other way to solve the problem, so that I can
get all values, true values and false values according to my choice?
 
Switch the query to SQL View (View menu in query design), and set up the
WHERE clause like this:
WHERE ([Forms]![frmSearch]![nophoto] Is Null)
OR ([nophoto] = [Forms]![frmSearch]![nophoto])

If the text box is blank, the first part of the condition is true for all
records. If it has something in it, the value is compared to the value of
the field.

For Access (JET) tables, use -1 for True, and 0 for False.

If you actually have several fields on your form that you need to match, it
would be more efficient to leave the criteria out of the query, and apply
them at runtime instead. If that interests you, there is a sample database
you can download here for Access 2000 and later:
http://allenbrowne.com/unlinked/Search2000.zip
 
Back
Top