Number of locations open between 2 dates

  • Thread starter Thread starter D. M.
  • Start date Start date
D

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.
 
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.
 
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
 
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
 
Back
Top