start date & end date - show all date in between

[Nancy Tang]

I have a date table & a leave table which leave table contains startdate &
end_date. How i do show all date in between startdate & end date . For
example : Leave startdate = 1/5/2010, end date =3/5/2010. i want the data
show all date - 1/5/2010 NANCY AL
2/5/2010 NANCY AL
3/5/2010 NANCY AL

 

[Allen Browne]

You can use a Cartesian Product query to generate a record for every date in
the period.

1. Create a table with just one field of type Number, named (say) CountID,
and mark it as primary key. Save the table as (say) tblCount.

2. Enter records from zero to the highest number of days someone will have
off. If you need more than a couple of dozen records, you can use the code
in this link to create the data:
http://allenbrowne.com/ser-39.html

3. Create a query that uses both your leave table and tblCount. In the upper
pane of query design, there should be no line joining these 2 tables. This
gives you every possible combination of the two.

4. In the Field row enter this expression:
LeaveDate: [startdate] + tblCount.CountID
In the Criteria row under this enter:
<= [end date]

5. Output the staff name field as well.

 

[PieterLinden via AccessMonster.com]

I have a date table & a leave table which leave table contains startdate &
end_date. How i do show all date in between startdate & end date . For
example : Leave startdate = 1/5/2010, end date =3/5/2010. i want the data
show all date - 1/5/2010 NANCY AL
2/5/2010 NANCY AL
3/5/2010 NANCY AL

SELECT Leave.PersonID, Leave.LeaveStart, Leave.LeaveEnd, TableOfDates.TheDate
FROM TableOfDates, Leave
WHERE (((TableOfDates.TheDate) Between [Leave].[LeaveStart] And [Leave].
[LeaveEnd]));

You don't really need the Leave.LeaveStart and Leave.LeaveEnd fields in there.
.. I just put them in to make sure my math was right... Note that there is
NO join between the two tables... that's deliberate. Basically, I'm creating
a deliberate Cartesian product and then filtering it with the Where clause.