Query for Received and Closed Date Range

[swalsh84]

I built a report request menu to dynamically build a SQL query for Access to run a report. The user selects the parameters and the form builds the query on the fly and then changes the record set for the report to pull the right data.

The query building was coming along swimmingly until the users requested a date range. I have a date range selection that can look at any cases within the date range. This part is not working currently. This is what the form generates. Get all cases with these specific parameters within this date range.

SELECT qry_All_Records.* FROM qry_All_Records
WHERE (CompanyCode Like '*' Or CompanyCode Is Null)
AND (ResponsiblePerson Like '*' Or ResponsiblePerson Is Null)
AND (CaseType Like '*' Or CaseType Is Null)
AND (LPSStatus Like '*' Or LPSStatus Is Null)
AND (Division Like '*' Or Division Is Null)
AND (ProductLine Like '*' Or ProductLine Is Null)
AND (CaseStatus IN('Closed','Open','Other','Pending - Legal','Pending - Production','Pending - Quality Review'))
AND (BusinessLine Like '*' Or BusinessLine Is Null)
AND (Source Like '*' Or Source Is Null)
AND (SourceDetail Like '*' Or SourceDetail Is Null)
AND ([ReceivedDate] Between #09/01/11# AND #09/30/11# OR [ClosedDate] Between #09/01/11# AND #09/30/11#)

The problem ends up being that I get cases with received dates within the range and closed dates outside of the range or vice versa.

1. Does this make sense?
2. Any thoughts on how to fix this?

Thanks,

[June7]

Yes, that does make sense. The query is returning exactly what is asked for. If you want records where the RecievedDate is between the range and ClosedDate is also between the range, then change the OR to AND.

[swalsh84]

Slightly different approach approach. Basically what I wanted to accomplish is that if the case is not closed, then look at the received date only and get all cases within the date range. If the case is closed, get all cases that were closed within the date range and disregard the received date. This was accomplished successfully with the below query.

SELECT qry_All_Records.* FROM qry_All_Records
WHERE (CompanyCode Like '*' Or CompanyCode Is Null)
AND (ResponsiblePerson Like '*' Or ResponsiblePerson Is Null)
AND (CaseType Like '*' Or CaseType Is Null)
AND (LPSStatus Like '*' Or LPSStatus Is Null)
AND (Division Like '*' Or Division Is Null)
AND (ProductLine Like '*' Or ProductLine Is Null)
AND (CaseStatus IN('Closed','Open','Other','Pending - All Other Servicing','Pending - Default','Pending - ERC','Pending - Legal','Pending - Production','Pending - Quality Review'))
AND (BusinessLine Like '*' Or BusinessLine Is Null)
AND (Source Like '*' Or Source Is Null)
AND (SourceDetail Like '*' Or SourceDetail Is Null)
AND ((([ReceivedDate] BETWEEN #09/01/11# AND #09/30/11#) AND [ClosedDate] Is Null)
OR ([ClosedDate] BETWEEN #09/01/11# AND #09/30/11#))