Dates Headings In Crosstab Report

[Projectnow]

hi,

I have a crosstab query that views all the vacation days for employees between two dates parameter,
the column headings of the query is the all the dates between the parameters even if they are nulls

I need to design a report for that query where the headings to be dynamic for dates in the headings and doesn't exceed 31 days.

PARAMETERS d DateTime, b DateTime;
TRANSFORM Sum(Vaclist.Period) AS SumOfPeriod
SELECT Vaclist.EmpID, Vaclist.Vactype, Sum((nnz([period]))) AS Total
FROM (qryEmployeeWorkDates LEFT JOIN Vaclist ON (qryEmployeeWorkDates.DateOfPeriod = Vaclist.VacDate) AND (qryEmployeeWorkDates.EmpID = Vaclist.EmpID)) LEFT JOIN tbl_VacType ON Vaclist.VacTypeID = tbl_VacType.VacTypeID
WHERE (((qryEmployeeWorkDates.DateOfPeriod) Between [d] And [b]))
GROUP BY Vaclist.EmpID, tbl_VacType.Vactype, Vaclist.Vactype
PIVOT qryEmployeeWorkDates.DateOfPeriod;

I attached an image that describes what I need to do

I really appreciate your help

[twinnyfo]

ProjectNow,

One idea is to have a separate table, called, for example, tblDateAdd, with one field, AddToDate. This table would have 30 records in it. Those 31 records are merely the numbers 0-30. Then, in your CrossTab query, add that table, with no relationships. However, your column heading will be the StartDate (I think that is your d variable) + AddToDate. This will give you 31 dates, starting with your StartDate and ending with StartDate + 30. Then, you should be able to format the dates in whatever way you need.

You may have to tweak things in order toget the exact results you need with your data, but this should get you mocing in the right direction.

Hope this idea helps.

[Projectnow]

thank you for your reply
one more question is there a way to color the column of the days off

[twinnyfo]

I don't know what version of Access you are using, but on the Form or Report, you should be able to use some type of Conditional Formatting.

[Projectnow]

thank you very much for your reply
I attached an image on my first post in this thread, I used unbound controls for this report, so I can't figure out how to handle coloring the column of the days off and holidays

any help would be appreciated
thank you very much