Number of locations open between 2 dates

[D. M.]

Hello,

I have a database of store locations. Each location has an open date as
well as a closed date (dates are formatted 00/00/0000). If the store is
still open the closed date is blank. There is also a status field (open or
closed).

I am trying to find out how many locations were opened in a certain month
and year.

Can someone help me with a query for this?

Thanks.

 

[Beetle]

Select * From SomeTable
Where OpenDate Between #12/1/2007# And #12/31/2007#

 

[D. M.]

Thank you for your response Sean. I don't think I explained my problem
properly.

I've been asked how many stores were open and operating in October 2006.
Example: If the store opened 12/15/1999 and closed 01/15/2007, it was open
in October 2006.

I'm not sure how to write that query. How many stores were open in October
2006??

Thanks again for your help.

 

[Jeff Boyce]

Are you saying a store is "open" during a particular time period (e.g.,
"October, 2006") if it's [OpenedDate] falls on/before the last day of that
time period, and the [ClosedDate] is null or falls after the last day of
that time period?

I believe you could use those selection criteria in your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

SELECT Count(*)
FROM StoresTable
WHERE OpenDate <=#2007-10-31#
and (CloseDate >=#2007-10-01# Or CloseDate is Null)

Alternative which may be a bit slower with LARGE datasets
SELECT Count(*)
FROM StoresTable
WHERE OpenDate <=#2007-10-31#
and Nz(CloseDate,#2999-12-31#) >=#2007-10-01#

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