Getting data when BOTH boolean boxes are false

[Kizinti]

Greetings to all,
Well if the Title is not obscure enough, I bet the question will be even worse.

In our access table we have two Boolean columns. We have a query that is set up so ...

PH1 PH2 Results
1) T F data returned where PH1 = True
2) F T data returned where PH2 = True
3) T T data returned where PH1 = True AND PH2 = True

The issue is that when neither are checked (both are false) we get NO results (because there is not a case where both PH1 and PH2 are both false), and what we want is to get everything. In other words...
4) F F All data

What would be your suggestion?

THanks,
Kizinti

 

[Joe4]

Are these text boxes on a Form?
Please post any VBA code associated with the Form and the SQL code associated with your query.

 

[Kizinti]

They are both check boxes in the form, and here is the code that we use for the query

SELECT DISTINCTROW tblIssues.IssueID, tblIssues.IssueDescription, tblElement.ElementName, tblIssues.IssueClosed, tblIssues.IssuePriority, tblIssues.IssueReportDate, tblIssues.IssueTORNumber, tblIssues.IssueCPCRNumber, tblIssues.IssueStatus, tblIssues.Retest, tblIssues.Phase1, tblIssues.Phase2
FROM tblIssues INNER JOIN tblElement ON tblIssues.ElementID = tblElement.ElementID
WHERE (((tblIssues.IssueDescription) Like "*" & Forms![Query Issues]!Text0 & "*") And ((tblElement.ElementName) Like "*" & Forms![Query Issues]!Text7) And ((tblIssues.IssueClosed)=Yes) And ((tblIssues.IssuePriority) Like "*" & Forms![Query Issues]!Text2 & "*") And ((tblIssues.IssueReportDate) Like "*" & Forms![Query Issues]!Text4 & "*") And ((tblIssues.IssueTORNumber) Like "*" & Forms![Query Issues]!Text25 & "*") And ((tblIssues.IssueCPCRNumber) Like "*" & Forms![Query Issues]!Text27 & "*") And ((tblIssues.IssueStatus) Like "*" & Forms![Query Issues]!Text23 & "*") And ((tblIssues.Phase1) Like "*" & Forms![Query Issues]!Phase1 & "*") And ((tblIssues.Phase2) Like "*" & Forms![Query Issues]!Phase2 & "*") And ((Forms![Query Issues]!Text29) Is Null)) Or (((tblIssues.IssueID)=Forms![Query Issues]!Text29) And ((tblIssues.IssueDescription) Like "*" & Forms![Query Issues]!Text0 & "*") And ((tblElement.ElementName) Like "*" & Forms![Query Issues]!Text7) And ((tblIssues.IssueClosed)=Yes) And ((tblIssues.IssuePriority) Like "*" & Forms![Query Issues]!Text2 & "*") And ((tblIssues.IssueReportDate) Like "*" & Forms![Query Issues]!Text4 & "*") And ((tblIssues.IssueTORNumber) Like "*" & Forms![Query Issues]!Text25 & "*") And ((tblIssues.IssueCPCRNumber) Like "*" & Forms![Query Issues]!Text27 & "*") And ((tblIssues.IssueStatus) Like "*" & Forms![Query Issues]!Text23 & "*"))
ORDER BY tblIssues.IssueID;

Also note, the reason why we didn't as of yet create new queries is because we already have 4 queries performing different variations on data collected, and we would have to double or triple the quires to accommodate this. We are hoping a simple change in the query is all we need.

Thanks again

 

[Joe4]

Also note, the reason why we didn't as of yet create new queries is because we already have 4 queries performing different variations on data collected, and we would have to double or triple the quires to accommodate this. We are hoping a simple change in the query is all we need.

I would approach this a different way, which would also solve this problem of all these extra queries.

What I usually do is create a selection Form, where users can select the Criteria they like (not too different from what you have now). However, instead of passing those Form values directly to the Query, I actually use VBA code to create the SQL code for the query on the fly. This gives you more flexibility to have it behave exactly like you want, and eliminates the need for all these extra queries. Here is a link to a post in which I helped someone do this: http://www.mrexcel.com/forum/micros...c-applications-sql-statement.html#post3099809

It actually isn't as hard as you may think. If you create any query manually and switch to SQL View, you can see the SQL code it requires. So you are just building this string in VBQ and then applying that code to a query.

One shortcut that I often use is that I will create a query that returns all the records I have, but without any Criteria (no WHERE clause). Let's call it Query1. Then, when I am building my SQL code in VBA, I start out with "SELECT * FROM Query1 WHERE ...". It just saves me from having to put all those fields in the Select clause in VBA if I have already done that in another query.

 

[Kizinti]

We'll give this a try. I'll let you know how this works out.
Thanks so much

 

[Micron]

I must be missing something. Why don't you just pull back the data and forget about the T/F values? To me, it looks like you want the data regardless of the situation since you appear to have listed every possibility and noted that you want the data in those cases. Sorry if I've missed the boat.