How to group two AND clauses together

[Archie999]

Hi,

I have the following query that I cannot get to work.

Select * from tblIssueIncident LEFT OUTER JOIN tblIssueUpdate ON tblIssueIncident.MyIssueIncidentID=tblIssueUpdate.IssueIncidentID where [tblIssueIncident.SiteID]= 87 AND [tblIssueIncident.StatusID] IN(1 , 2, 99) AND ([tblIssueIncident.StatusID] = 2 AND [IssueCloseDate] > Date() - 7);

This is a dynamic query that is build based on a form. The tricky part has to do with the last 2 parts of the query...

One of the fields that the user can select one or more of, to be included in the resulting report, is 'Status' (1=Open, 2=Closed, 3=Pending, ect). However, I want to give users the option to always print 'Closed' (StatusID = 2) issues that were closed in the last week (that is, the IssueCloseDate > Todays Date - 7 days). You can see that I have parenthasized the last clause in the statement in the hopes that it would consider the 2 parts together :

([tblIssueIncident.StatusID] = 2 AND [IssueCloseDate] > Date() - 7)

It is not working. Could someone tell me how to tell the query to take these 2 query clauses together?

Hope this made sense. Thanks in advance!

Arch

 

[WayneRyan]

Archie,

Right now, all of your clauses must be true.

You will only print records where:

SiteID = 87 And
StatusID In (1,2,99) And
StatusID = 2 AND
IssueCloseDate is within the last week.

I don't think you intend to retrieve ONLY the ones
closed, with IssueCloseDate within the last week.

I think you're needing IF (A AND B) Or (C AND D).

Need clarification.

Wayne

 

[Archie999]

Hi Wayne,

You are right that I do not want to retreive ONLY the ones closed, with an IssueClosedDate within the last week. I want to retreive as follows:

A) SiteID = 87
B) StatusID IN (1,3,99)
C) StatusID = 2
D) IssueClosed Date within the last week

Retrieve IF (A and B) Or (A and C and D)

Do you see what I mean? My first example was probably confusing because I used StatusID 2 in the criteria for B) which did not really make sense. In fact, on the front end of the app if the user select Status 2 (Closed) then I disable the option to include closed items from the past week.

Still having trouble making this work. Appreciate your thoughts!

Arch

 

[WayneRyan]

Archie,

Select * 
from tblIssueIncident 
LEFT OUTER JOIN tblIssueUpdate ON tblIssueIncident.MyIssueIncidentID=tblIssueUpdate.IssueIncidentID 
where ([tblIssueIncident.SiteID]= 87 AND 
       [tblIssueIncident.StatusID] IN (1, 2, 99)) Or
      ([tblIssueIncident.SiteID]= 87 AND
       [tblIssueIncident.StatusID] = 2 AND 
       [IssueCloseDate] > Date() - 7);

Wayne

 

[Archie999]

Hi again ...

Thanks for your response. I have implemented what you have suggested and am stumped as to why it is not giving me the expected results.

Here is my query now:

Select * from tblIssueIncident LEFT OUTER JOIN tblIssueUpdate ON tblIssueIncident.MyIssueIncidentID=tblIssueUpdate.IssueIncidentID where ([tblIssueIncident.SiteID]= 87 AND [tblIssueIncident.StatusID] IN(1 , 99) ) OR ([tblIssueIncident.SiteID]= 87 AND [tblIssueIncident.StatusID] = 2 AND [tblIssueIncident.IssueCloseDate] > 6/20/04);

The first part of the statement before the OR works as expected. As for the second part, I am being returned ALL Closed (StatusID=2) issues not just the ones whose close date is greater than the inputted date (in this case 6/20/04).

Do you see any flaw in my query that would explain this? I tried taking out the second StatusID criteria since if an IssueCloseDate exists then the issue must be closed. So then it looked like this:

Select * from tblIssueIncident LEFT OUTER JOIN tblIssueUpdate ON tblIssueIncident.MyIssueIncidentID=tblIssueUpdate.IssueIncidentID where ([tblIssueIncident.SiteID]= 87 AND [tblIssueIncident.StatusID] IN(1 , 99) ) OR ([tblIssueIncident.SiteID]= 87 AND [tblIssueIncident.IssueCloseDate] > 6/20/04);

Same results. Hoping I'm overlooking something here 'cause I'm baffled!

Let me know if you have any suggestions. Thanks again

Arch

 

[WayneRyan]

Arch,

Select * 
from tblIssueIncident 
LEFT OUTER JOIN tblIssueUpdate ON tblIssueIncident.MyIssueIncidentID=tblIssueUpdate.IssueIncidentID 
where ([tblIssueIncident.SiteID]= 87 AND 
       [tblIssueIncident.StatusID] IN(1 , 99)) OR 
      ([tblIssueIncident.SiteID]= 87 AND 
       [tblIssueIncident.StatusID] = 2 AND 
       [tblIssueIncident.IssueCloseDate] > #6/20/04#);

Make sure IssueCloseDate is *really* a Date/Time field.

Wayne

 

[Archie999]

It works

Thanks Wayne, that did it. Totally forgot about those pesky date identifier thingies.

Arch