Query/Report on dates between begin and end

  • Thread starter Thread starter scifinut
  • Start date Start date
S

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?
 
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.
 
Back
Top