Query/Report on dates between begin and end

[scifinut]

I have a table that records all time off for my employees. To simplify
entry, I used "begindate" and "enddate" for consecutive days. I have created
a crosstab query and report that gives a year-in-review snapshot of an
employee's time off..rows are the months, columns are the numeric days of the
month (i.e., 1, 2, 3, etc). My problem is that because I can only use one
field as a column head, my report only shows the first day of a range, for
instance VacationTaken (begindate)010109 to (enddate)010509, would only
result in my query/report displaying a "V" on 010109. Is there anyway to
write a query that would return all dates between begindate and enddate, that
could then be used to create the crosstab?

 

[KARL DEWEY]

It appears that your begindate and enddate are text fields so you can not do
math on them without converting. They should be DateTime datatype.

This will convert the date --
DateSerial("20"& Right([begindate],2), Right(Left([begindate],4),2),
Left([begindate],2))

Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through your maximum spread.

Then use this query ahead of the crosstab --
SELECT EmpID, DateAdd("d", CountNUM, DateSerial("20"& Right([begindate],2),
Right(Left([begindate],4),2), Left([begindate],2))) AS VacationDate
FROM YourTable
WHERE CountNUM <= DateDiff("d", DateSerial("20"& Right([begindate],2),
Right(Left([begindate],4),2), Left([begindate],2)), DateSerial("20"&
Right([enddate],2), Right(Left([enddate],4),2), Left([enddate],2)))+1;

Test the above query before trying the crosstab to make sure I put the
correct number of parenthesis.