P
Paul B.
Hello all...
Right now I have 4 queries that each have a WHERE clause. Each query is
basically the same, except the WHERE part. I would like to know if I can add
the WHERE part when the user selects the criteria in the form, so I can use
just one query.
Here is the query:
___________________________________
SELECT Abs(Sum([Form2]="Filed")) AS Form2Filed, Abs(Sum([CAMT]="Filed")) AS
CAMTFiled, Abs(Sum([AEDDL]="Filed")) AS AEDDLFiled, Abs(Sum([Form2]="Req &
Rec")) AS Form2ReqRec, Abs(Sum([CAMT]="Req & Rec")) AS CAMTReqRec,
Abs(Sum([AEDDL]="Req & Rec")) AS AEDDLReqRec, Abs(Sum([Form2]="Outstanding"))
AS Form2Outstanding, Abs(Sum([CAMT]="Outstanding")) AS CAMTOutstanding,
Abs(Sum([AEDDL]="Outstanding")) AS AEDDLOutstanding, Abs(Sum([Form2]="Not
Required")) AS Form2NotRequired, Abs(Sum([CAMT]="Not Required")) AS
CAMTNotRequired, Abs(Sum([AEDDL]="Not Required")) AS AEDDLNotRequired,
Abs(Sum(IsNull([Form2]))) AS Form2NoInfo, Abs(Sum(IsNull([CAMT]))) AS
CAMTNoInfo, Abs(Sum(IsNull([AEDDL]))) AS AEDDLNoInfo, Count(*) AS Total
FROM tblEpistry LEFT JOIN tblUnit1 ON tblEpistry.EpistryID =
tblUnit1.EpistryID
WHERE
(((tblEpistry.DateOfService)>=[Forms]![frmQuery_BCAS_Crew_Reports]![txtStartDate])
AND
((tblEpistry.DateOfService)<=[Forms]![frmQuery_BCAS_Crew_Reports]![txtEndDate])
AND ((tblEpistry.CallType)=[Forms]![frmQuery_BCAS_Crew_Reports]![CallType])
AND ((tblUnit1.Region)=Forms![frmQuery_BCAS_Crew_Reports]![Region] ) AND
((tblUnit1.Station)=Forms![frmQuery_BCAS_Crew_Reports]![Station]) AND
((tblUnit1.Shift)=Forms![frmQuery_BCAS_Crew_Reports]![Shift] ) AND
((tblUnit1.ShiftNumber)=Forms![frmQuery_BCAS_Crew_Reports]![ShiftNumber] ));
________________________________________________
I would like to break the WHERE part out of the above and put it behind the
code in the On-Click event.
Any suggestions would be appreciated.
Cheers
Right now I have 4 queries that each have a WHERE clause. Each query is
basically the same, except the WHERE part. I would like to know if I can add
the WHERE part when the user selects the criteria in the form, so I can use
just one query.
Here is the query:
___________________________________
SELECT Abs(Sum([Form2]="Filed")) AS Form2Filed, Abs(Sum([CAMT]="Filed")) AS
CAMTFiled, Abs(Sum([AEDDL]="Filed")) AS AEDDLFiled, Abs(Sum([Form2]="Req &
Rec")) AS Form2ReqRec, Abs(Sum([CAMT]="Req & Rec")) AS CAMTReqRec,
Abs(Sum([AEDDL]="Req & Rec")) AS AEDDLReqRec, Abs(Sum([Form2]="Outstanding"))
AS Form2Outstanding, Abs(Sum([CAMT]="Outstanding")) AS CAMTOutstanding,
Abs(Sum([AEDDL]="Outstanding")) AS AEDDLOutstanding, Abs(Sum([Form2]="Not
Required")) AS Form2NotRequired, Abs(Sum([CAMT]="Not Required")) AS
CAMTNotRequired, Abs(Sum([AEDDL]="Not Required")) AS AEDDLNotRequired,
Abs(Sum(IsNull([Form2]))) AS Form2NoInfo, Abs(Sum(IsNull([CAMT]))) AS
CAMTNoInfo, Abs(Sum(IsNull([AEDDL]))) AS AEDDLNoInfo, Count(*) AS Total
FROM tblEpistry LEFT JOIN tblUnit1 ON tblEpistry.EpistryID =
tblUnit1.EpistryID
WHERE
(((tblEpistry.DateOfService)>=[Forms]![frmQuery_BCAS_Crew_Reports]![txtStartDate])
AND
((tblEpistry.DateOfService)<=[Forms]![frmQuery_BCAS_Crew_Reports]![txtEndDate])
AND ((tblEpistry.CallType)=[Forms]![frmQuery_BCAS_Crew_Reports]![CallType])
AND ((tblUnit1.Region)=Forms![frmQuery_BCAS_Crew_Reports]![Region] ) AND
((tblUnit1.Station)=Forms![frmQuery_BCAS_Crew_Reports]![Station]) AND
((tblUnit1.Shift)=Forms![frmQuery_BCAS_Crew_Reports]![Shift] ) AND
((tblUnit1.ShiftNumber)=Forms![frmQuery_BCAS_Crew_Reports]![ShiftNumber] ));
________________________________________________
I would like to break the WHERE part out of the above and put it behind the
code in the On-Click event.
Any suggestions would be appreciated.
Cheers