Date Range Query Problem

[rukk]

Hi,
I need to write a query that retrieves records from table based on the date range that i specify.

eg: My table will have a CouponStartDate and CouponEndDate. When i specify the date range i need to get all the Coupons that were active during that period. I have tried this query like this

select * from tblCoupon where WHERE (((StartDate)>=[Enter Start Date] And (EndDate)<=[Enter End Date])) or (((EndDate)>=[Enter Start Date] And (EndDate)<=[Enter End Date]));

when i run this query with this range (01/20/04 and 02/15/04)i am getting all the records that started or ended between these dates but the Coupons started before 01/20/04 and ended after 02/15/04 are not coming, but those are still active in this date range, so i need to get them. How to solve it?

 

[dhookom]

Try:
select * from tblCoupon where WHERE StartDate<=[Enter End Date] And EndDate>=[Enter Start Date];


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[rukk]

small mistake in the query that i have specified above

select * from tblCoupon where WHERE (((StartDate)>=[Enter Start Date] And (EndDate)<=[Enter End Date])) or (((StartDate)>=[Enter Start Date] And (EndDate)<=[Enter End Date]));

 

[rukk]

Thank you so much. I got it. It worked for me. Thanks a lot.

 

[rukk]

A small problem with this is when the CouponStartDate stated and ended between the daterange i have specfied it not pulling that.

For Example:
If the date range is
CouponStartDate <= 01-JAN-2003 and CouponEndDate >= 31-Jan-2003.

It is pulling all the records that were active between that frame like
15-Nov-2002 to 25-Jan-2003
25-Jan-2002 to 15-Feb-2003

But not this
5-Jan-2003 to 25-Jan-2003.

How to do that?

 

[dhookom]

It seems that you have swapped the start and end date that I had suggested earlier.
WHERE CouponStartDate<=[Enter End Date] And CouponEndDate>=[Enter Start Date];


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[rukk]

Thanks a lot, i was swaping. Thanks a lot for helping so quickly.