query

[Guest]

Help! I need to write a query that will select records in 2 different date
ranges:
all records between 2003/07/07 and 2003/12/31, and between 2004/01/01 and
2004/06/30. The query I have written between #2003/07/07# and #2003/12/31#
And between #2004/01/01# and #2004/06/30# gives me all records in each date
range and what i need is only those records which fall in BOTH date ranges.
I'm a rookie flying by the seat of my pants, I hope someone can help me!

 

[Ted Allen]

Hi Jayne,

I'm not completely clear on what you are trying to do.
Generally a date in a record would be in one date range
or the other, but not both. I'm guessing that you are
actually looking for some value (such as a CompanyID)
that is contained in records found in both date ranges.

If that is the case, I believe you should be able to
enter subquery criteria beneath the field in question
(say CompanyID), such as the following:

IN (SELECT CompanyID FROM YourTable WHERE YourDateField
BETWEEN #Date1# AND #Date2#) AND IN (SELECT CompanyID
FROM YourTable WHERE YourDateField BETWEEN #Date3# AND
#Date4#)

In your example the date ranges were adjacent, so it was
really one big range, but I assume that won't always be
the case.

Another way to do the same thing would be to create
separate queries selecting the CompanyID's for each date
range, then add both of those as the data source for a
third query, joined by an inner join. That would give
only the ID's in both ranges, but requires 3 queries.

Hopefully that will help, post back if I misunderstood
your question.

-Ted Allen

 

[John Spencer (MVP)]

I don't understand how one date can fall in both ranges since the ranges are
mutually exclusive. Obviously I am missing some information here.