Boolean filtering in a recordset query

I'm having trouble with a query that needs to filter for checkbox type data.  At the bottom is the whole query but the part I'm having trouble with is
            " AND (tblPersons.DA = " & Me.chkDA_Lab & _
             " OR tblPersons.RA = " & Me.chkRA_Lab & _
             " OR tblPersons.DTL = " & Me.chkDTL_Lab & ")" & _

I have three checkboxes that correspond to 3 boolean (yes/no) fields.  I want to select the field when the checkbox is checked but not filter at all if the checkbox is not check.  In other word, I don't want to filter for false, only true.   What I have doesn't work because it's filtering for false and maybe doing other guffy things.  Anyhow, is there a way to use a wildcard to select everything (either yes or no).  I tried the following but no worky:
             " AND (tblPersons.DA = " & "'*'" & _
             " OR tblPersons.RA = " & True & _
             " OR tblPersons.DTL = " & "'*'" & ")" & _


---------------------- Entire Query ----------------------------------------
  strQuery = "SELECT tblTimeRA.TimeCategoryID AS TimeCategoryID, Sum(([tblTimeRA]![Minutes]/60)) AS [Time(hrs)]" & _
             " FROM (tblPersons INNER JOIN (tblProjects INNER JOIN tblTimeRA ON tblProjects.ProjectID = tblTimeRA.ProjectID)" & _
             " ON tblPersons.PersonID = tblTimeRA.PersonID) INNER JOIN tblTimeCategories ON tblTimeRA.TimeCategoryID =" & _
             " tblTimeCategories.TimeCategoryID" & _
             " WHERE tblTimeRA.Date >= " & "#" & Nz(Me.txtStartDate, "1/1/1900") & "#" & _
             " And tblTimeRA.Date <= " & "#" & Nz(Me.txtEndDate, "1/1/2099") & "#" & _
             " AND tblProjects.ProjectAccount LIKE " & "'" & Nz(Me.txtProjectAccount, "*") & "'" & _
             " AND tblProjects.AuthorCode LIKE " & "'" & Nz(Me.txtauthorcode, "*") & "'" & _
             " AND tblPersons.LastName LIKE " & "'" & Nz(Me.cboLastName, "*") & "'" & _
             " AND tblPersons.FirstName LIKE " & "'" & Nz(Me.txtFirstName, "*") & "'" & _
             " AND (tblPersons.DA = " & Me.chkDA_Lab & _
             " OR tblPersons.RA = " & Me.chkRA_Lab & _
             " OR tblPersons.DTL = " & Me.chkDTL_Lab & ")" & _
             " GROUP BY tblTimeRA.TimeCategoryID"
schmir1Asked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
> I want to select the field when the checkbox is checked but not filter at all if the checkbox is not check.

Try this:

             " AND (ABS(tblPersons.DA) >= " & Abs(Me.chkDA_Lab) & _
             " OR ABS(tblPersons.RA) >= " & Abs(Me.chkRA_Lab) & _
             " OR ABS(tblPersons.DTL) >= " & Abs(Me.chkDTL_Lab) & ")" & _

/gustav
0
 
kiprimshotCommented:
wouldnt you just use  = "YES"?
0
 
kiprimshotCommented:
Is this based on the form or the table value?  

If its on the table value I would play around with it in query view until you have the results you want... and then you can convert it to the string for your strQuery variable
without quotations it would look like

AND (tblPersons.DA = "YES"
              OR tblPersons.RA = "YES"
             OR tblPersons.DTL = "YES"

that is ... if I understand you correctly.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
NestorioCommented:
Have the checkboxes setted a default value?
0
 
NestorioCommented:
If not assigned a default value, set it, or try this:


            " AND (tblPersons.DA = Nz(" & Me.chkDA_Lab & _
             ",0) OR tblPersons.RA = Nz(" & Me.chkRA_Lab & _
             ",0) OR tblPersons.DTL = Nz(" & Me.chkDTL_Lab & ",0))" & _
0
 
Jokra_the_BarbarianCommented:
If your fields are stored as boolean values, then you should check for 0 and -1:
             " AND (tblPersons.DA = '*'" & _
             " OR tblPersons.RA = -1" & _
             " OR tblPersons.DTL = '*')" & 
0
 
schmir1Author Commented:
Thanks for the suggestions but couldn't get any to work.  I think the problem is that the query shouldn't filter at all on those checkboxes that are uncheck.  Only if it's check then filter for all those records that are also checked.  For example:

If me.chkda_Lab is checked then
include only those with tblPersons.DA checked (or yes, whatever you want to call it).  

Don't exclude any other records even if RA or DTL are checked or not checked.  I was thinking I could do some preproccessing with code.  If checked then use it as is.  If not checked then substitute some wildcard that would not exclude any records.  The fact that there are 3 checkboxes complicates the problem even farther.
0
 
Jokra_the_BarbarianCommented:
Is your data in SQL Server or MS Access? Cactus' query should work.  If using this string to execute directly in SQL Server, then it would fail because of the '>='. It would have to be re-written as:

            " AND ((ABS(tblPersons.DA) > " & Abs(Me.chkDA_Lab) & " OR ABS(tblPersons.DA) = " & Abs(Me.chkDA_Lab)  & ")" & _
             " OR (ABS(tblPersons.RA) > " & Abs(Me.chkRA_Lab) & " OR ABS(tblPersons.RA) = " & Abs(Me.chkRA_Lab)   & ")" & _
             " OR (ABS(tblPersons.DTL) > " & Abs(Me.chkDTL_Lab) & " OR ABS(tblPersons.DTL) = " & Abs(Me.chkDTL_Lab) & "))" & _

0
 
Gustav BrockCIOCommented:
Hmm - works fine here. I guess something else is going on - like Null values or so.

/gustav
0
 
schmir1Author Commented:
I'm using an Access DB.

It doesn't look like it filters out anything.  I get the same records returned no matter what checkboxes are checked?

This is what the query looks like in debug mode with all checkboxes checked:

SELECT tblTimeRA.TimeCategoryID AS TimeCategoryID, Sum(([tblTimeRA]![Minutes]/60)) AS [Time(hrs)] FROM (tblPersons INNER JOIN (tblProjects INNER JOIN tblTimeRA ON tblProjects.ProjectID = tblTimeRA.ProjectID) ON tblPersons.PersonID = tblTimeRA.PersonID) INNER JOIN tblTimeCategories ON tblTimeRA.TimeCategoryID = tblTimeCategories.TimeCategoryID WHERE tblTimeRA.Date >= #10/1/2004# AND tblTimeRA.Date <= #11/1/2004# AND tblProjects.ProjectAccount LIKE '*' AND tblProjects.AuthorCode LIKE '*' AND tblPersons.LastName LIKE '*' AND tblPersons.FirstName LIKE '*' AND (ABS(tblPersons.DA) >= 1 OR ABS(tblPersons.RA) >= 1 OR ABS(tblPersons.DTL) >= 1) GROUP BY tblTimeRA.TimeCategoryID


This is what the query looks like in debug mode with no checkboxes checked:

SELECT tblTimeRA.TimeCategoryID AS TimeCategoryID, Sum(([tblTimeRA]![Minutes]/60)) AS [Time(hrs)] FROM (tblPersons INNER JOIN (tblProjects INNER JOIN tblTimeRA ON tblProjects.ProjectID = tblTimeRA.ProjectID) ON tblPersons.PersonID = tblTimeRA.PersonID) INNER JOIN tblTimeCategories ON tblTimeRA.TimeCategoryID = tblTimeCategories.TimeCategoryID WHERE tblTimeRA.Date >= #10/1/2004# AND tblTimeRA.Date <= #11/1/2004# AND tblProjects.ProjectAccount LIKE '*' AND tblProjects.AuthorCode LIKE '*' AND tblPersons.LastName LIKE '*' AND tblPersons.FirstName LIKE '*' AND (ABS(tblPersons.DA) >= 0 OR ABS(tblPersons.RA) >= 0 OR ABS(tblPersons.DTL) >= 0) GROUP BY tblTimeRA.TimeCategoryID


Actually, I would like the query to return all when none or all checkboxes are checked.  Unfortunately, all records are returned when one checkbox is checked (DA should return no records) as follows:

SELECT tblTimeRA.TimeCategoryID AS TimeCategoryID, Sum(([tblTimeRA]![Minutes]/60)) AS [Time(hrs)] FROM (tblPersons INNER JOIN (tblProjects INNER JOIN tblTimeRA ON tblProjects.ProjectID = tblTimeRA.ProjectID) ON tblPersons.PersonID = tblTimeRA.PersonID) INNER JOIN tblTimeCategories ON tblTimeRA.TimeCategoryID = tblTimeCategories.TimeCategoryID WHERE tblTimeRA.Date >= #10/1/2004# AND tblTimeRA.Date <= #11/1/2004# AND tblProjects.ProjectAccount LIKE '*' AND tblProjects.AuthorCode LIKE '*' AND tblPersons.LastName LIKE '*' AND tblPersons.FirstName LIKE '*' AND (ABS(tblPersons.DA) >= 1 OR ABS(tblPersons.RA) >= 0 OR ABS(tblPersons.DTL) >= 0) GROUP BY tblTimeRA.TimeCategoryID
0
 
Gustav BrockCIOCommented:
It looks right, but I can see the logic is wrong as the checkboxes should filter independantly.

So try this modification:

             " AND ((ABS(tblPersons.DA) >= " & Abs(Me.chkDA_Lab) & ")" & _
             " OR (ABS(tblPersons.RA) >= " & Abs(Me.chkRA_Lab) & ")" & _
             " OR (ABS(tblPersons.DTL) >= " & Abs(Me.chkDTL_Lab) & "))" & _

/gustav
0
 
schmir1Author Commented:
I found the workable solution:
             " AND ((tblPersons.DA = " & True & " AND tblpersons.DA =" & Me.chkDA_Lab & ")" & _
             " OR (tblPersons.RA = " & True & " AND tblpersons.RA =" & Me.chkRA_Lab & ")" & _
             " OR (tblPersons.DTL = " & True & " AND tblpersons.dtl =" & Me.chkDTL_Lab & "))" & _

The first part only includes the true cases then it just compare those true case to whatever the checkbox is.

Thanks for you help.  It got me thinking.
0
 
Gustav BrockCIOCommented:
OK. That's another way round that.

/gustav
0
All Courses

From novice to tech pro — start learning today.