Query all dates

  • Thread starter Thread starter Amin
  • Start date Start date
A

Amin

I have two textboxes in my form: StartDate and EndDate. I also have a table
with a field that has each member of my team. What I am trying to produce is
a query that lists all dates from the StartDate to the EndDate with each
member of my team. So if my team consisted of Moe, Larry, and Curly, and my
StartDate was 02/02/2009 and my EndDate was 02/04/2009, my query would
produce this:

Moe 02/02/2009
Moe 02/03/2009
Moe 02/04/2009
Larry 02/02/2009
Larry 02/03/2009
Larry 02/04/2009
Curly 02/02/2009
Curly 02/03/2009
Curly 02/04/2009

As always, thanks in advance.

Amin
 
I have two textboxes in my form: StartDate and EndDate. I also have a table
with a field that has each member of my team. What I am trying to produce is
a query that lists all dates from the StartDate to the EndDate with each
member of my team. So if my team consisted of Moe, Larry, and Curly, and my
StartDate was 02/02/2009 and my EndDate was 02/04/2009, my query would
produce this:

Moe 02/02/2009
Moe 02/03/2009
Moe 02/04/2009
Larry 02/02/2009
Larry 02/03/2009
Larry 02/04/2009
Curly 02/02/2009
Curly 02/03/2009
Curly 02/04/2009

As always, thanks in advance.

Amin
Click to expand...

Use a criterion
= [Forms]![YourForm]![StartDate] AND < DateAdd("d", 1, [Forms]![YourForm]![EndDate]))
Click to expand...

The dateadd bit handles the case where the date field contains a time portion.
 
Create a table named CountNumber with number field CountNUM containing all
numbers from 0 (zero) through your highest date spread.
Then this query --
SELECT YourTable.[Name], DateAdd("d",[CountNUM],[StartDate]) AS [All Dates]
FROM YourTable, CountNumber
WHERE DateAdd("d",[CountNUM],[StartDate]) Between [StartDate] And [EndDate]
ORDER BY YourTable.[Name], DateAdd("d",[CountNUM],[StartDate]);
 
Back
Top