How to search query for a check box

[Hulas]

There are some fields that have check box. Now, how do I search a query such that it will prompt the user to enter Yes or No, so that when I select Yes it will give me the records that are checked and vice-versa for No.

Thank you
Hulas

[NeoPa]

As this doesn't really make sense as written, I'll guess that you're asking about how to select records (using a query) where the user has clicked on a field which shows as a Checkbox in a form.

Assuming the CheckBox field is called [chkYN], you would use :

...
WHERE [chkYN]

...to find records which are checked and :

...
WHERE Not [chkYN]

...to find records which are NOT checked.

Does this answer what you were trying to ask?

[Hulas]

Sorry for not being so descriptive about that. I have a table whose one of the field is a check box. Now, what I have done is I have created a form that has a list box with 'Yes' or 'No'. The form also has a button call 'Ok'. Now, how should I programme the 'OK' button such that if I select 'Yes' from the list box than it will give me all the records for the field whose check box are selected. Likewise, if I select 'No' from the list box than it will give me all the records for the field whose records are not selected. Again, if I select both 'Yes' and 'No' than it should give me all the records.

Thank you for your help
Hulas

[NeoPa]

This really rather depends on what "it" is.

You use "it" throughout your explanation, yet never describe what "it" is.

If "it" is a report (or form) then you need to set up and pass a filter, yet if it is a query you will need to think again about your interface.

[PianoMan64]

Sorry for not being so descriptive about that. I have a table whose one of the field is a check box. Now, what I have done is I have created a form that has a list box with 'Yes' or 'No'. The form also has a button call 'Ok'. Now, how should I programme the 'OK' button such that if I select 'Yes' from the list box than it will give me all the records for the field whose check box are selected. Likewise, if I select 'No' from the list box than it will give me all the records for the field whose records are not selected. Again, if I select both 'Yes' and 'No' than it should give me all the records.

Thank you for your help
Hulas

So Hula,

Let me see if I understand your question, you have a list control box that has a list of results in it. It has both Yes and No Items in it. You want to be able to select a Combo box control with either Yes or No in it and then have th list control display the resulting records accordingly?

If so, then here's what you're going to need to do:

Like explained before, you're going to have to build a SELECT SQL String in the RowSource Property of the List Control Box.

Example:

Assuming the List Control named was "lstList" and the Combo Box was named "cboCriteria" then you would do something like this in Code section of the form.

 
If me.cboCriteria = "Yes" Then
me.lstList.rowsource = "SELECT * FROM [TableName] WHERE chkBox = True"
Else
me.lstList.rowsouce = "SELECT * FROM [TableName] WHERE chkBox = False"
End if
me.lstList.Requery
me.repaint
 

Paste this into the OnChange Event

Hope that helps,

Joe P.

[Hulas]

No, I don't think I have really found what I need. I will try to be more descriptive and let's see if that works. I have a table 'FoodandBeverage' and this table has a field called 'Restaurant' whose data type is Yes/No. Now, I want to set up a form that has a list box with (yes or no), such that if I select 'Yes' from the list and press the 'OK' button I should get all the records whose check boxes are checked. Likewise, I if I select 'No' from the list and press the 'OK' button I should get all the records whose check boxes are not-checked. I don't care if it pulls the records from a query or table.

Thank you guys for your help.
Hulas

[NeoPa]

I'm pretty sure I understand the logic of the requirement Hulas. What I still don't know is how you intend to process the data.

A table was never an option. A query or report or form are the options. If you want to use a form or report then you must have one to refer to.

Queries are harder to apply the necessary logic to as Access isn't very friendly in that area. Reports and forms on the other hand are relatively straightforward and involve passing a filter in the call to open them.

[NeoPa]

I will assume that you are happy to design a report for this and that it's called [rptFnB] with a boolean (Yes/No) field called [Restaurant].
The ListBox is called [lstYN] and the first line is "Yes", the second line "No".

Private Sub cmdOK_Click()
  Dim strFilter As String
  Dim intIx As Integer
 
  For intIx = 0 To Me.lstYN.ListCount - 1
    If Me.lstYN.Selected(intIx) Then _
      strFilter = strFilter & Me.lstYN.Column(0, intIx)
  Next intIx
  Select Case strFilter
  Case ""
    'Potentially use a MsgBox() here
    Exit Sub
  Case "YesNo"
    strFilter = ""
  Case Else
    strFilter = IIf(strFilter = "Yes", "", "Not ") & "[Restaurant]"
  End Select
  Call DoCmd.OpenReport("rptFnB",acViewPreview,,strFilter)
End Sub

Your options would be a lot simpler if you used two separate CheckBoxes on your form instead of a ListBox though. The query would be an option then. I suppose this just illustrates that it's helpful if you just answer the questions we ask rather than trying to explain to us why they're not important (It'll certainly save me having to waste so much time writing every option down). Hey-ho - I'm sure you'll learn.