Between Dates and All Dates for those Records

[Em D]

My database contains several dates for each person the dates are associated
with. I would like to have a query return all dates for each person ONLY if
they have a date between Date1 and Date2. (Date1 and Date2 would be
determined using parameters.)

 

[John Spencer MVP]

Use a subquery in the where clause to identify the persons that have records
in the specified time frame.

The SQL of the query would look something like the following:

Parameters [Enter Start Date] DateTime, [Enter End Date] DateTime;
SELECT PersonID, ActivityDates
FROM SomeTable
WHERE PersonID in
(SELECT PersonID
FROM SomeTable
WHERE SomeDateField between [Enter Start Date] and [Enter End Date])

If you don't know how to use the SQL window to construct a query, post back
with your existing query (View: SQL then copy and paste into the posting).

Then hopefully someone can modify the existing query. Or ask for step-by-step
instructions on how to build the query in Query Design view.

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

 

[Jeff Boyce]

"How" depends on "what" ... what data structure are you using?

Do you have "several date [fields]" in your table, or a table with multiple
rows, each one containing one date value for one person?

Regards

Jeff Boyce
Microsoft Office/Access MVP