Date Criteria that falls within a date range

[lvjan777]

Access 2003 Windows XP Professional

I'm trying to open a report that displays all the records that are active during the user selected start and stop dates. I’m having trouble with the syntax since there are two input dates and two database dates to compare.

If they select 1 Apr 04 – 30 Apr 04 I want to see record 1.
If they select 1 May 04 – 30 May 04 I want to see record 1 and 2

Record 1
Start Date 12 Apr 04
Stop Date 30 Sep 07

Record 2
Start Date 15 May 04
End Date 15 July 05

Here's what I tried to use. It works if you select 1 Apr 04 - 30 Apr 04 but doesn't work if you select 1 May 04 - 30 May 04

DoCmd.OpenReport stDocName, acViewPreview, , (startdate & " between " & Me.txtStartDate & " and " & Me.txtStopDate & " Or " & stopdate & " between " & Me.txtStartDate & " and " & Me.txtStopDate & " Or " & Me.txtStartDate & " between " & startdate & " and " & stopdate & " Or " & Me.txtStopDate & " between " & startdate & " and " & stopdate)

[Rabbit]

Assuming you have no nulls:
StartDate <= txtEndDate AND EndDate >= txtStartDate

Also, you might have to delimit your dates using pound signs.

[lvjan777]

Assuming you have no nulls:
StartDate <= txtEndDate AND EndDate >= txtStartDate

Also, you might have to delimit your dates using pound signs.

Thanks for your help. I'm still having problems with this though. It works for some dates, but not others. Here is the sql. I don't know where to delimit the dates with pound signs. I tried cdate on the date coming from the form but that didn't help.

My first record has a start date of 4/12/04 and an end date of 8/30/07. I input 4/1/04 and 4/30/04 as the date ranges, but it doesn't return the first record. Both criteria are met so it should return the record.
4/12/04 <= 4/30/04 and 9/30/07 >= 4/1/04


SELECT tblOSSProject.Prj_Name, tblOSSProject.prj_Description, tblOSSProject.prjXPRMsnDate, tblOSSProject.prjEndDate
FROM tblOSSProject
WHERE (((tblOSSProject.prjXPRMsnDate)<=[forms]![frmselectdates].[txtstopdate]) AND ((tblOSSProject.prjEndDate)>=[forms]![frmselectdates].[txtstartdate]));

[Rabbit]

Hmm, the SQL looks fine. I would try checking the data types of the 2 fields in the table and the two text boxes in the form and make sure they're of date data type.

[lvjan777]

Hmm, the SQL looks fine. I would try checking the data types of the 2 fields in the table and the two text boxes in the form and make sure they're of date data type.

I hard coded the dates in the query and it works fine with # around the date. I'm passing in the dates from a form with two unbound text boxes. I was testing the query in the QBE and didn't get the results I expected. When I pass in the dates from the form, it works. Thanks for your help.

[Rabbit]

I hard coded the dates in the query and it works fine with # around the date. I'm passing in the dates from a form with two unbound text boxes. I was testing the query in the QBE and didn't get the results I expected. When I pass in the dates from the form, it works. Thanks for your help.

Not a problem, good luck.