2 Seperate Date Ranges in query criteria

V

Vickster

I am trying to create a query in design view that pulls records that have
reports that have been run between 1/1/2003 to 12/31/2003 AND 1/1/2004 to
12/21/2004. There has to be a report in each year to be considered true. I
cannot find anything that I can use to link both of these date ranges to the
same criteria. Help! Thanks. Vickster
 
K

Ken Snell [MVP]

You'll need to explain your table's data and field structure. It's obviously
not possible for a single field to have more than one date value in the same
record, so either you're looking for a joined table that contains more than
one record, or you have multiple fields that must meet your criteria for a
single record.
 
V

Vickster

I have a table that contains a userid (number) and a report date. The report
date field is in the 1/01/2004 format. I need to pull all of the records
that have reports in both 2003 AND 2004. I have the criteria string started
as: Between #1/1/2003# and #12/31/2003# - how do I add the year 2004
criteria. I want the statement to be true in both years. Thanks.
 
J

John Vinson

I am trying to create a query in design view that pulls records that have
reports that have been run between 1/1/2003 to 12/31/2003 AND 1/1/2004 to
12/21/2004. There has to be a report in each year to be considered true. I
cannot find anything that I can use to link both of these date ranges to the
same criteria. Help! Thanks. Vickster
Click to expand...

The EXISTS clause is helpful here:

WHERE EXISTS (SELECT recordid FROM records WHERE rundate BETWEEN
#1/1/2003# AND #12/31/2003#)
AND EXISTS (SELECT recordID from records WHERE rundate BETWEEN
#1/1/2004# AND #12/31/2004#)


John W. Vinson[MVP]
 
V

Vickster

Perfect. Thanks John, I will give it a try.

John Vinson said:
The EXISTS clause is helpful here:

WHERE EXISTS (SELECT recordid FROM records WHERE rundate BETWEEN
#1/1/2003# AND #12/31/2003#)
AND EXISTS (SELECT recordID from records WHERE rundate BETWEEN
#1/1/2004# AND #12/31/2004#)


John W. Vinson[MVP]
Click to expand...
 
Top