Need help creating Access 2003 Query criteria

Posted on 2012-08-30
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

    Author Comment

    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 47

    Accepted Solution

    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

    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

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

    Author Comment

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now