Query all dates

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
 
J

John W. Vinson

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

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

The dateadd bit handles the case where the date field contains a time portion.
 
K

KARL DEWEY

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]);
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Between dates 7
Looking up across date ranges in Access 2
sql statement 2
Lost in time! 5
Running Sum in Query 2
Update Query - Consecutive Numbers 9
Query Problem... 1
Problem with displaying query as a 2D grid 10

Top