Subquery for calculated field - probs with crosstab
- From: "Tony Maddox" <tmaddox@xxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 6 Jun 2005 21:39:12 +0100
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
.
- Prev by Date: Re: Yes/No Field in query
- Next by Date: update simultaneously a field in a group of records
- Previous by thread: Show/hide columns in design mode
- Next by thread: update simultaneously a field in a group of records
- Index(es):
Relevant Pages
|
