Query/Report on dates between begin and end


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?
 
Ad

Advertisements

K

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.
 

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

Top