Problem with Date Range Query 1

[octotech]

Hi all,

I have parameters defined to query for records within the date range entered and to return all records when no dates are specified:

WHERE ((([Problem Tracker].Date) Between [Start Date] And [End Date]) AND (([Start Date]) Is Not Null) AND (([End Date]) Is Not Null)) OR ((([Problem Tracker].Date)>=[Start Date]) AND (([Start Date]) Is Null) AND (([End Date]) Is Null)) OR ((([Problem Tracker].Date)<=[End Date]) AND (([Start Date]) Is Not Null) AND (([End Date]) Is Not Null)) OR ((([Start Date]) Is Null) AND (([End Date]) Is Null))

Whew! It works great with null values, but doesn't seem to acknowledge the start date. For example:

Date Range Entered Dates of Records Returned
6/1/01-6/10/01 6/8/01
6/10/01-6/15/01 6/8/01, 6/12/01
6/12/01-6/12/01 6/8/01, 6/12/01
7/1/01-7/30/01 6/8/01, 6/12/01, 6/18/01, 7/21/01

Any suggestions? BTW this is a great forum!

TIA,

JB

 

[tlbroadbent]

I've made some slight modifications to the query. These should help you obtain the result you seek. I added some comments which you should remove before using the query. I also aligned it differently for readability and removed the extra, unneeded parentheses added by Access.

' The first part iof the query looks OK to me
WHERE ([Problem Tracker].Date Between [Start Date] And [End Date]
AND [Start Date] Is Not Null
AND [End Date] Is Not Null)

' No changes in the next section
OR ([Problem Tracker].Date>=[Start Date]
AND [Start Date] Is Not Null ' You should be able to remove this line but it doesn't hurt.
AND [End Date] Is Null)

' The significant change occurred in the next portion of the query. I think the test should be [Start Date] Is Null rather than [Start date] Is Not Null.
OR ([Problem Tracker].Date<=[End Date]
AND [Start Date] Is Null
AND [End Date] Is Not Null)

' No changes in the last section of the query criteria
OR ([Start Date] Is Null
AND [End Date] Is Null)

I hope I've understood correctly. Let me know if this works. Terry

http://members.home.net/tlbroadbent/prog.htm

Neither success nor failure is ever final. -Roger Babson

 

[octotech]

Actually I got it working- I eliminated the Not Null statements so it looks like:

WHERE [Problem Tracker].Date Between [Start Date] And [End Date] OR ([Problem Tracker].Date>=[Start Date] AND [End Date] is null) OR ([Problem Tracker].Date<=[End Date] AND [Start Date] Is Null) OR ([Start Date] Is Null and [End Date] Is Null);

Works perfectly. Thanks for a quick response.

JB