Results 1 to 14 of 14
  1. #1
    RobD is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    7

    Check Box question

    I have a yes/no field that I want to link a check box to that will show all data with a "yes" value when checked, but show the "yes" AND "no" data when it isn't checked. So far I've only been able to get one, or the other.


    Can someone tell me if this is possible, and explain to me how if it is?

    Thanks,
    Rob

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    How are you just showing the "yes" values?

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845
    something like

    SELECT *
    FROM myTable
    WHERE boolfield=chkbox or chkbox=false

  4. #4
    RobD is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    7
    Quote Originally Posted by RuralGuy View Post
    How are you just showing the "yes" values?

    My form has the check boxes, and I created a query button that shows the yes values in table form.

  5. #5
    RobD is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    7
    Ajax, can you tell me where to enter the suggestion to mentioned? I'm very much a novice with Access.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845
    depends on your how you want it to work - you said

    So far I've only been able to get one, or the other.
    So what are you using to do this?

  7. #7
    RobD is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    7
    Quote Originally Posted by Ajax View Post
    depends on your how you want it to work - you said

    So what are you using to do this?
    It would probably help if I defined what I am hoping to accomplish. I will use shoes as a product reference because terminology of our products may be unfamiliar to most people.

    Our shoes can have many options. Some come with shoe laces, or heels. Some are brown, black, or blue. Some will be a combination of shoe laces, heels, and brown, while others could have no laces and they're blue. There could be many, many combinations including sizes.

    In my form I have a checkbox for each of the options. If I want to find a shoe that is between size 8 and 10, with laces, and is blue, I would enter the min and max size, then check the boxes for laces, and blue. I then hit the command button "Show Shoes as a table".

    Here is he problem I run into when I run this scenario. I just want to list all shoes, with ALL options, between the sizes of 8 and 10. So I enter the size range in my form, and I leave the check boxes unchecked. However, since the unchecked box reports as a "no" value, it does not report all the shoes between 8-10 that have ALL the options, it will only report the shoes between 8-10 with NO options.

    What I'm trying to do, is find a way to say yes when the box is checked, but basically ignore the the checkbox completely if it is unchecked. Is that possible?

    (Below are screen shots of what I currently have)
    I created a form. Within the form I created a checkbox, and a command button that launches a query.
    Click image for larger version. 

Name:	Form.jpg 
Views:	14 
Size:	36.4 KB 
ID:	25177
    The query looks back at the form to the associated checkbox, and if it's checked, it lists all the items in the associated table with the field equal to yes.
    Click image for larger version. 

Name:	Query.jpg 
Views:	14 
Size:	11.1 KB 
ID:	25175
    Click image for larger version. 

Name:	Query Results.jpg 
Views:	14 
Size:	4.9 KB 
ID:	25176
    Attached Thumbnails Attached Thumbnails Form.jpg  

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,700
    You will need to custom build the query with code.
    dim sSQL as string
    sSQL = "Select whatever from tblWhatever WHERE SHOE_Size between me.this AND me.that" 'use your existing code here
    'add items to sSQL string only as needed...
    if chkUnivFrame = true then
    sSQL = sSQL & " AND chkUnivFrame = true"
    endif
    if chkSCMold = true then
    sSQL = sSQL & " AND chkSCMold = true"
    endif
    ...
    and so on

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845
    where in your query you have Forms![Frame Preferences]![Waterwrapped], change it to

    Code:
    Forms![Frame Preferences]![Waterwrapped] OR Forms![Frame Preferences]![Waterwrapped]=False
    you should just be able to copy and paste the above
    Last edited by CJ_London; 07-15-2016 at 08:18 AM. Reason: error in code

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845
    where in your query you have Forms![Frame Preferences]![Waterwrapped], change it to

    Code:
    Forms![Frame Preferences]![Waterwrapped] OR Forms![Frame Preferences]![Waterwrapped] =False
    you should just be able to copy and paste the above

  11. #11
    RobD is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    7
    Quote Originally Posted by Ajax View Post
    where in your query you have Forms![Frame Preferences]![Waterwrapped], change it to

    Code:
    Forms![Frame Preferences]![Waterwrapped] OR Forms![Frame Preferences]![Waterwrapped] =False
    you should just be able to copy and paste the above
    I changed the lines to what you recommended, but something strange is happening. The text is reverting back to what was there previously, and also adding an additional column with the old text. See the pic.
    Click image for larger version. 

Name:	Query_changing.jpg 
Views:	15 
Size:	12.5 KB 
ID:	25182

    I've saved it after making the change, and I've even deleted the column completely, closed Access, and reopened the database. But every time I go back into the query, it's changed.

  12. #12
    RobD is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    7
    Quote Originally Posted by davegri View Post
    You will need to custom build the query with code.
    dim sSQL as string
    sSQL = "Select whatever from tblWhatever WHERE SHOE_Size between me.this AND me.that" 'use your existing code here
    'add items to sSQL string only as needed...
    if chkUnivFrame = true then
    sSQL = sSQL & " AND chkUnivFrame = true"
    endif
    if chkSCMold = true then
    sSQL = sSQL & " AND chkSCMold = true"
    endif
    ...
    and so on

    I'm a novice, and not really sure how to build my query from what you wrote.

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845
    The text is reverting back to what was there previously
    what is displayed is perfectly OK - try the query.

    All the query builder does is provide a visual view of what the SQL looks like and change the SQL as you change the view. In this case it has interpreted the sql differently as to how it is displayed.

  14. #14
    RobD is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2016
    Posts
    7
    Thanks Ajax! That seems to work!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-03-2014, 12:16 PM
  2. Replies: 5
    Last Post: 09-23-2013, 08:37 AM
  3. Noob Question macro to check checkbox
    By bradmitchell in forum Access
    Replies: 1
    Last Post: 05-13-2012, 10:08 PM
  4. Check Box easy question
    By kathi2005 in forum Forms
    Replies: 7
    Last Post: 11-03-2011, 01:58 PM
  5. Check box question.
    By BwillieS in forum Access
    Replies: 1
    Last Post: 02-28-2011, 09:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums