Need help creating Access 2003 Query criteria

Posted on 2012-08-30
Medium Priority
Last Modified: 2012-08-30
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.
Question by:anctech
  • 4

Author Comment

ID: 38352068
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.
LVL 49

Accepted Solution

Dale Fye earned 2000 total points
ID: 38352095
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

Author Comment

ID: 38352128
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.

Author Comment

ID: 38352147
Ok,  I figured out this has to be in the SQL view but still not sure about the triple state.

Author Comment

ID: 38352274
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.

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question