Using checkboxes in queries with parameters derived from forms.

[RichardM]

This should be a simple problem to resolve but so far it has stumped me.
I have created a single table query.
I am using a form so the user can select which data they wish to select, i.e., query by example.
The input form includes one drop down box and 4 check boxes. The table columns included in the query are one short text data type, and 4 yes/no data types (I assume are binary).
The fields in the input form match the data types of the columns in the query.
Using the expression builder, I have inserted the fields from the form into the parameter row of the query. I have set the default value of the checkbox fields to "No"

But, it doesn't work. All attempts to query returns no records. I have tried a number of variations with no success.

So, what am I missing?

[ranman256]

If you need complex form filters THEN make a query from it, do this:
NOTE: THE QUERY MUST ALREADY EXIST.

Public Sub btnReport_Click()
Dim sSql As String, sWhere As String
Dim qdf As querydef
Const kQRY = "qsFormFilter"    'here is the query we use ...in the report too

sWhere = "1=1"
    'the query is built depending on the various filters the user picks...
If Not IsNull(cboState) Then sWhere = sWhere & " and [state]='" & cboState & "'"
If (chkIsRegisterd) Then sWhere = sWhere & " and [Registered]=true "
If (chkContact) Then sWhere = sWhere & " and [Contact]=" & chkContact.Value


'IF you just want to filter records
if sWhere = "1=1" then
  me.filterOn = false
else
  me.filter = sWhere
  me.filterOn = true
endif


'or     'BUILD the query from the 'where'
Set qdf = currentdb.querydefs(kQRY)
qdf.Sql = "SELECT * FROM tblCompany WHERE " & sWhere
qdf.Close

 'open the query or report here!
DOCMD.openquery kQRY
'or open the report based on the query
'DOCMD.OPENREPORT "rMyReport"
SET qdf = nothing
End Sub

[ssanfu]

@RichardM,
What you are asking is kinds like asking "I tried to get to Joe's house. I followed the instructions, but ended up at the Post Office". What did I do wrong?"

It is helpful to provide the dB, a sample db, the SQl of the query, an image if the table/relationships, etc.
So maybe you would post the SQL?
You might/will get a more focused answer....

[RichardM]

This should be a simple problem to resolve but so far it has stumped me.
I have created a single table query.
I am using a form so the user can select which data they wish to select, i.e., query by example.
The input form includes one drop down box and 4 check boxes. The table columns included in the query are one short text data type, and 4 yes/no data types (I assume are binary).
The fields in the input form match the data types of the columns in the query.
Using the expression builder, I have inserted the fields from the form into the parameter row of the query. I have set the default value of the checkbox fields to "No"

But, it doesn't work. All attempts to query returns no records. I have tried a number of variations with no success.

So, what am I missing?

I am going to try to submit a reply:
Database is too large to upload. I will have to make a sample.

[RichardM]

I placed the code into an onClick event on a button of the form.
It threw the following error, "You canceled the previous operation" at this point in the code "DoCmd.OpenQuery kQRY"

[RichardM]

I placed the code into an onClick event on a button of the form.
It threw the following error, "You canceled the previous operation" at this point in the code "DoCmd.OpenQuery kQRY"

[RichardM]

@RichardM,
What you are asking is kinds like asking "I tried to get to Joe's house. I followed the instructions, but ended up at the Post Office". What did I do wrong?"

It is helpful to provide the dB, a sample db, the SQl of the query, an image if the table/relationships, etc.
So maybe you would post the SQL?
You might/will get a more focused answer....

Here is a sample,
PrepareSample.accdb

[RichardM]

Not sure what the problem was but I rebuilt the search form and everything stated to work. So, for now, the problem is resolved.

[ssanfu]

Great. But I have to tell you there are a number of other issues you should take care of.

Good luck with your project and welcome to the forum....