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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
NestorioCommented:
Have the checkboxes setted a default value?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
Gustav BrockCIOCommented:
> 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.