Need help creating Access 2003 Query criteria

I am trying to figure out how to create a query that will only show results that meets a specific criteria.     I have attached a screen shot to show you the fields I am working with.    I need a query that will ONLY show records that first fall into a date range and secondly contain one or more fields that are checked mark as yes.       If non of the yes/no fields are checked as yes, then the record should not be displayed in the query results.    There are 10 YES/NO fields in each record.   Even if only one of them are checked yes, then the records needs to be included BUT if non are checked yes, then the record should not be included.

Does anyone know of a way to do this?

Thank you.
anctechAsked:
Who is Participating?
 
Dale FyeCommented:
With Yes/No fields like you have, I would write the query something like:

WHERE [Patient_Eval] BETWEEN [StartDate] AND [EndDate] AND
[YesNo1] + [YesNo2] +[YesNo3] + ... + [YesNo10] <> 0

since Yes/No fields store 0s and 1s, this should work.  However, if your Yes/No fields allow triple state (includes NULL) then you would have to wrap each of those in a NZ( ) function, like:

WHERE [Patient_Eval] BETWEEN [StartDate] AND [EndDate] AND
NZ([YesNo1],0) + NZ([YesNo2],0) +NZ([YesNo3],0) + ... + NZ([YesNo10], 0) <> 0
0
 
anctechAuthor Commented:
Attached is a screen shot of my current query that isn't giving me the results I need.  Hopeing someone can look at this and suggest a way to make this work.  Thank you.
Example-of-Query.JPG
0
 
anctechAuthor Commented:
How can I check on this triple state factor your referring to for the Yes/No Fields?      This might be a dumb question, but where would I put the query language you noted above?

Also, thank you for the prompt reply.
0
 
anctechAuthor Commented:
Ok,  I figured out this has to be in the SQL view but still not sure about the triple state.
0
 
anctechAuthor Commented:
GOT IT, I was able to use your first example so I am assuming the triple state thing your referring to didn't matter in this case.

Thank you very much for your comment, as it certainly helped me get this working.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.