Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Criteria (2003)

    I have a query that has several yes/no fields. The fields get their criteria from a form that has a check boxs to indicate yes/no. How can I get the query to show all records if none of the check boxs on the form are checked. Right now I'm doing a test criteria on one field only but when I leave the box unchecked it won't show any records that have a "no" in that field. Hope that was clear enough.

    Thanks,

    Kent

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Query Criteria (2003)

    That doesn't seem very logical (it's different with combo boxes). Better put a command button on the form to tick all check boxes.

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (2003)

    Based on Hans response, I

  4. #4
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (2003)

    Thanks Hans and Tom for responding. I think Han's is right. I need to go back and rethink my logic on this. I was hoping there was a way that in the query criteria I could enter a formula that if I left all the check boxes unchecked on the form that all records would show in the query. As Tom says if my criteria is being received from the form then an unchecked box is going to show as a "no" for the criteria so it would only show records unchecked.

    I want the yes/no fields in the query to get their criteria from the form but lets say I have 10 yes/no fields in my query and I only want 5 of those fields to receive criteria from the form one time and maybe a different set of yes/no fields receive criteria from the form another time. In other words each time I use the from I want to specify which of the 10 fields will use criteria and that criteria will be "yes". Any suggestions on that? What would I put in the query criteria to reference that info from the form?

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query Criteria (2003)

    I enclose a sample with one way to something similar to what you describe.

    Instead of having a query that takes criteria from a form, a criteria string is built in code, then a form is opened filtered by that string.

    The function fnBuildCriteria checks each of the check boxes on the form and ignores those that are not checked.

    edited later
    I have had another think about this and now post a modifed version. As well as doing what the previous one did, this one uses DAO to rewrite the sql of a query based on the selections on the form. Again it still uses the function fnBuildCriteria
    Regards
    John



  6. #6
    Star Lounger
    Join Date
    Apr 2004
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Criteria (2003)

    John,

    This looks like exactly what I'm trying to accomplish. Thanks so much for your post. I've got tweak it a little bit to fit my application but this should do the trick. Thanks for all your help!

    Kent

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •