CrossTab Query & Dates

[sara82]

I have a crosstab query with the following columns which calculates the number of days an employee took to perform a task.

Field: LogDate
Table: tblWorkLog
Total: Count
Corsstab: Value

Is there anyway to exclude weekends and holidays from the value?

I have a table, tblHolidays, with the field, HolidayDate. Is there a function that I can use like the WeekDay and use that with the HolidayDate field so that I can only get the "Work Days"?

 

[lupins46]

There is a function to calculate working days, taking account of holiday dates, here:

http://www.mvps.org/access/datetime/date0006.htm

I think it is easier to do the workdays calculation in a query and then base the crosstab on that query.

 

[sara82]

Thank you for the site. That is referring to 2 dates. I have only one date. I aplogize for not mentioning that.

What's happening is the user logs in and inputs what he did for the day, and the date is logged.

So the crosstab query sums up those days, but I would like to exclude weekends and holidays.

 

[lupins46]

Then start from a query like:

Select * from mytable where weekday(logdate) <>1 and weekday(logdate) <>7 and logdate not in (Select HolidayDate from tblholidays)