Subquery for calculated field - probs with crosstab

T

Tony Maddox

Hi

I have a query which calculates the number of working days between two
dates, [ReqDate] (the earlier date) and [RepDate]. The SQL uses the
following DateDiff/IIf construction, I believe due to Doug Steele, which
works fine:

PARAMETERS [Forms]![frmTurnaroundAll]![txtBeginDate] DateTime,
[Forms]![frmTurnaroundAll]![txtEndDate] DateTime,
[Forms]![frmTurnaroundAll]![cboCaseType] Text ( 255 );
SELECT tblCases.CaseNo, tblCases.ReqDate, tblCases.RepDate,
IIf(IsNull([VerPathologist]),"UNKNOWN",[VerPathologist]) AS Pathologist,
1+DateDiff("d",[ReqDate],[RepDate])-DateDiff("ww",[ReqDate],[RepDate])*2-IIf(Weekday([RepDate],1)=7,IIf(Weekday([ReqDate],1)=7,0,1),IIf(Weekday([ReqDate],1)=7,-1,0))
AS WorkDayTurnaround
FROM tblCases
WHERE (((tblCases.CaseNo) Like Forms!frmTurnaroundAll!cboCaseType & "*") And
((tblCases.ReqDate) Between Forms!frmTurnaroundAll!txtBeginDate And
Forms!frmTurnaroundAll!txtEndDate))
ORDER BY tblCases.CaseNo;

Case No is the primary key in tblCases and is a text field. I add 1 at the
beginning of the calculation to make it inclusive of the beginning and end
date. I also would like to exclude public holidays, so I have a table,
tblHolidays, with a field HolidayDate such that if I construct the subquery

SELECT Count(*) AS HolidayCount FROM tblHolidays WHERE
(((tblHolidays.HolidayDate) Between tblCases.ReqDate And tblCases.RepDate))

and subtract it from the main expression, it works fine in this query but
not in any crosstab based on it. Is there any way round this or do I have to
use VBA?

Tony
 
Top