query criteria

  • Thread starter Thread starter Lauren B.
  • Start date Start date
L

Lauren B.

I have a series of checkboxes on a form that represent several steps in a
process. As each step is completed, the user will check the appropriate box.

I am attempting to write a query that allows users to search by last
completed step. As Access denotes a "checked" check box as -1, I am
considering that in my query criteria. However, I am encountering problems
as if a check box is mistakenly "checked" and subsequently "unchecked", a 0
is recorded.

If, for example, the user was querying to see which clients had completed
step 3 (but no further), the query was written with steps 1 and 2 blank,
have step 3 equal to "-1", and have steps 4 and 5 "null". This approach
works; however, it leaves out those with a "0" in steps 4 and 5.

How can I write my query to search for entries that have a "-1" in step 3
and EITHER nothing ("is null") or a "0" in steps 4 and 5.

Thank you in advance for any assistance.
 
Interesting, is the checkbox bound to a Yes/No field (usually can only have only
one of two values - True or False - and defaults to false.)? Or is it bound to
a number field of some type - can store null, 0, or -1?

Try using an IIF statement or the NZ function.

Where Step3 = True and Nz(Step4,False) = False

Or
WHERE Step3 = True AND IFF(Step4 is Null,False,Step4) = False

Or more complex, but perhaps faster

WHERE Step3= True AND (Step4 is Null Or Step4 = False)
 
Back
Top