Check Box question

[RobD]

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

[RuralGuy]

How are you just showing the "yes" values?

[CJ_London]

something like

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

[RobD]

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.

[RobD]

Ajax, can you tell me where to enter the suggestion to mentioned? I'm very much a novice with Access.

[CJ_London]

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?

[RobD]

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.

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.

[davegri]

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

[CJ_London]

where in your query you have Forms![Frame Preferences]![Waterwrapped], change it to

Forms![Frame Preferences]![Waterwrapped] OR Forms![Frame Preferences]![Waterwrapped]=False

you should just be able to copy and paste the above

[CJ_London]

where in your query you have Forms![Frame Preferences]![Waterwrapped], change it to

Forms![Frame Preferences]![Waterwrapped] OR Forms![Frame Preferences]![Waterwrapped] =False

you should just be able to copy and paste the above

[RobD]

where in your query you have Forms![Frame Preferences]![Waterwrapped], change it to

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.


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.

[RobD]

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.

[CJ_London]

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.

[RobD]

Thanks Ajax! That seems to work!