Solved

Boolean filtering in a recordset query

Posted on 2004-10-22
445 Views
Last Modified: 2008-02-07
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"
0
Question by:schmir1
    13 Comments
     
    LVL 2

    Expert Comment

    by:kiprimshot
    wouldnt you just use  = "YES"?
    0
     
    LVL 2

    Expert Comment

    by:kiprimshot
    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
     
    LVL 16

    Expert Comment

    by:Nestorio
    Have the checkboxes setted a default value?
    0
     
    LVL 16

    Expert Comment

    by:Nestorio
    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
     
    LVL 11

    Expert Comment

    by:Jokra_the_Barbarian
    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
     
    LVL 48

    Accepted Solution

    by:
    > 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
     

    Author Comment

    by:schmir1
    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
     
    LVL 11

    Expert Comment

    by:Jokra_the_Barbarian
    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
     
    LVL 48

    Expert Comment

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

    /gustav
    0
     

    Author Comment

    by:schmir1
    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
     
    LVL 48

    Expert Comment

    by:Gustav Brock
    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
     

    Author Comment

    by:schmir1
    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
     
    LVL 48

    Expert Comment

    by:Gustav Brock
    OK. That's another way round that.

    /gustav
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Cisco Complete Network Certification Training

    If you’re an IT engineer or technician, it's time you take your career to the next level. This elite training bundle is brimming with all of the information you need to learn to sit for Cisco CNNA, CCNP, and CCENT certification exams.

    Suggested Solutions

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now