Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Boolean filtering in a recordset query

Posted on 2004-10-22
13
Medium Priority
?
489 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
Comment
Question by:schmir1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
13 Comments
 
LVL 2

Expert Comment

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

Expert Comment

by:kiprimshot
ID: 12382329
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
ID: 12382366
Have the checkboxes setted a default value?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 16

Expert Comment

by:Nestorio
ID: 12382568
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
ID: 12382646
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 52

Accepted Solution

by:
Gustav Brock earned 1500 total points
ID: 12383276
> 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
ID: 12384667
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
ID: 12387195
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 52

Expert Comment

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

/gustav
0
 

Author Comment

by:schmir1
ID: 12396087
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 52

Expert Comment

by:Gustav Brock
ID: 12397992
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
ID: 12402147
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 52

Expert Comment

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

/gustav
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

610 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