Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2004
    Posts
    8

    Unanswered: Reset check box before running query

    I am performing an update query. The field to be updated is a Yes/No (Checkbox) field.

    In the query, I have a field ("Purchase Price") where I enter the criteria which determine which boxes in the Yes/No field should be checked.

    Let's say, for example, that I want boxes checked in the Yes/No field for all records with a Purchase Price of >=200.

    That works fine.

    But then, later I decide I only want boxes checked for records with Purchase Price >=250. After running the query, the boxes between 200 and 250 are (not surprisingly) still checked.

    HERE'S MY QUESTION: How do I reset all the check boxes each time I run the query, so that only the criteria for that query run (and not previous runs) shows up in my Yes/No column?

    If you need more info, just ask.

    Thanks!!

  2. #2
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    Just create a query that uncheck the boxes.

    Create an update query with that table
    Put the check box field in to update
    Set the update to to 0

    If you need it in SQL let me know

  3. #3
    Join Date
    Aug 2004
    Posts
    8
    I would like to do it all with one click. Thanks for the advice though.

  4. #4
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    You can do it with one click?

    Run two queries with one click

    Run the first query that resets
    Run the second query that updates the ticks?

    Its easy, if you dont know code then use a macro to run the queries?

  5. #5
    Join Date
    Aug 2004
    Posts
    8

    Thanks anyways

    I've been using Access for about one week, and I have no other database or coding experience. I wasn't sure if there was a way to do this, but apparently there isn't. Silly me.

  6. #6
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    It really wont cause a problem if you create two queries?

    Create two

    One to reset, one to update

    Create a macro

    In the macro open both queries, putting the reset one first and the update one second.

    On your form create a button that opens that macro and it will run both queries for you.

    If you want i will create you a quick sample that will demonstrate it for you and then you can adapt it. I can post a zip file on with the mdb in?

  7. #7
    Join Date
    Aug 2004
    Posts
    8

    Thanks Chris

    That makes sense. I'm going to do it that way - thanks for helping.

  8. #8
    Join Date
    Aug 2004
    Posts
    8

    One more question

    I'm trying to make the 2nd query (the one with the new criteria) into a parameter query that prompts me for the criteria before running.

    Instead of putting >=200 in the criteria box, I put [Enter the Purchase Price criteria]. When I run it, the prompt comes up, but it won't allow me to enter >=200.

    Will the parameter query only allow you to enter a single number, like 200 for example, or should I be able to enter >=200, or even between 200 and 1000?

    Thanks.

  9. #9
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by gourmet_genius
    I'm trying to make the 2nd query (the one with the new criteria) into a parameter query that prompts me for the criteria before running.

    Instead of putting >=200 in the criteria box, I put [Enter the Purchase Price criteria]. When I run it, the prompt comes up, but it won't allow me to enter >=200.

    Will the parameter query only allow you to enter a single number, like 200 for example, or should I be able to enter >=200, or even between 200 and 1000?

    Thanks.
    Why not use an open form as the reference for query criteria.

    On the critetia line you place

    =>[Forms]![YourFormName]![TextBoxName]

    Mike

  10. #10
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    You could also enter into the parameter criteria

    >=[Enter your parameter]

    That will do greater than or equal to that parameter

  11. #11
    Join Date
    Jun 2011
    Posts
    11

    I could use some help with this same issue...

    Quote Originally Posted by ChrisGolden View Post
    Just create a query that uncheck the boxes.

    Create an update query with that table
    Put the check box field in to update
    Set the update to to 0

    If you need it in SQL let me know
    I could use the SQL on this one!

    Thanks!!!

Posting Permissions

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