Multi-query issue

[Antavas]

I have a data table that contains an open date and close date for each entry.
I've been asked to create a report that does the following 3 things:

allows a date range for when items are opened (easy enough based off the
open date)
allows a date range for when items are closed(also easy because of the
between [] and [])

now the real issue I'm having is this third item. Based off of whatever open
range picked in Item #1 I need to generate a list of issues opened prior to
whichever start date is chosen and out of those issues I need to show only
the ones that stayed open past the end date of the range.

I've gone around this a few different ways and I can't get it to work. I've
tried to do this as seperate quieries (the open date query and close date
query have been easy) I can't get this third one to work though. I just want
to be able to have a query that looks at the date range I put in, gives me
the cases opened before the start of my selected date and still open at the
end of my selected range.

I also need to turn this all into a report (hopefully in one report)

Can everything be done in 1 query?

 

[John Spencer]

The criteria should look something like the following.

WHERE [OpenedDate] <= [Start Date of Period] and [ClosedDate] >= [End Date of
Period]

I'm not sure what you are attempting to accomplish from your description.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Amy Blankenship]

I have a data table that contains an open date and close date for each
entry.
I've been asked to create a report that does the following 3 things:

allows a date range for when items are opened (easy enough based off the
open date)
allows a date range for when items are closed(also easy because of the
between [] and [])

now the real issue I'm having is this third item. Based off of whatever
open
range picked in Item #1 I need to generate a list of issues opened prior
to
whichever start date is chosen and out of those issues I need to show only
the ones that stayed open past the end date of the range.

I've gone around this a few different ways and I can't get it to work.
I've
tried to do this as seperate quieries (the open date query and close date
query have been easy) I can't get this third one to work though. I just
want
to be able to have a query that looks at the date range I put in, gives me
the cases opened before the start of my selected date and still open at
the
end of my selected range.

SELECT * FROM YourTable WHERE StartDate < Forms![YourForm]![StartDate] AND
EndDate > Forms![YourForm]![EndDateRangeClose]

?

HTH;

Amy

 

[Antavas]

Basically my first query allows me to submit a date range for all issue open
dates using the Between [] and [] criteria. The next step I'm trying to
accomplish is take the start date of that range and return all issues that
occurred before that date. These issues must also not have a close date that
falls within that date range.

I hope this clarifies my need. I know what I posted before was a bit
convoluted and thank you for your advice.