how do i make this parameter (date related)

[keun]

I have two dates for cases, on field is the date the case opened (Intake) and the other is the date the case closed. If I want to know what cases were active in a time period I need to do the following (we will use the entire 2003 as an example).

I need every case where:
If Intake Date is less than 1/1/2003 then Closed Date is greater than 1/1/2003.

And
If Intake Date is after 1/1/2003 then Intake Date is before 12/31/03.

Using these parameters I can get every case that was active in 2003. The question is, how to I put these dates in my query? I would like it to be a parameter query that asks for the first date and the second date (which in my example was 1/1/03 and 12/31/03, repectively).

Thanks!

 

[straybullet]

Between [Enter Start Date] and [Enter End Date] will cause prompts to appear for the missing criteria.

Let them hate - so long as they fear... Lucius Accius

 

[keun]

I understand the way that works but it seems that this will be more complex than that since there are the if...then statements and dealing with values in two fields.

 

[PCLewis]

If Intake Date is less than 1/1/2003 then Closed Date is greater than 1/1/2003.

And
If Intake Date is after 1/1/2003 then Intake Date is before 12/31/03.

I think you will find it easier to write SQL where clauses is you drop language like "and if" and replace it with "OR".

WHERE 
([Intake Date] < #1-Jan-2003# AND [Closed Date] > #1-Jan-2003#) 
OR
([Intake Date] > #1-Jan-2003# AND [Closed Date] < #31-Dec-2003#)

 

[keun]

That looks really cool. I gues the million dollar question is: where do I put that?