Excluding Data in a Query

[Guest]

I have the following query:

SELECT tblAMUWork.ID, tblAMUWork.DateRcd, tblAMUWork.TypeOfWork,
tblAMUWork.MISNumber, tblAMUWork.RptName, tblAMUWork.AmtRcd,
Nz([CompletedAmt],0)+Nz([CompletedAmt1],0)+Nz([CompletedAmt2],0)+Nz([CompletedAmt3],0)
AS TotalAmtCompleted, Nz([AmtRcd],0)-Nz([TotalAmtCompleted],0) AS Variance,
tblAMUWork.WorkNotCompleted
FROM tblAMUWork
WHERE (((tblAMUWork.CompletedDate) Is Not Null)) OR
(((tblAMUWork.CompletedDate1) Is Not Null)) OR (((tblAMUWork.CompletedDate2)
Is Not Null)) OR (((tblAMUWork.CompletedDate3) Is Not Null));


This query works fine except I don't want to see the information if the
variance is = to 0. I need to see if it is greater than or less than 0. Can
some please advise what criteria I need to enter in the query so that records
that contain 0 are not shown.

Thanks

 

[Rick Brandt]

I have the following query:

SELECT tblAMUWork.ID, tblAMUWork.DateRcd, tblAMUWork.TypeOfWork,
tblAMUWork.MISNumber, tblAMUWork.RptName, tblAMUWork.AmtRcd,
Nz([CompletedAmt],0)+Nz([CompletedAmt1],0)+Nz([CompletedAmt2],0)+Nz([CompletedAmt3],0)
AS TotalAmtCompleted, Nz([AmtRcd],0)-Nz([TotalAmtCompleted],0) AS Variance,
tblAMUWork.WorkNotCompleted
FROM tblAMUWork
WHERE (((tblAMUWork.CompletedDate) Is Not Null)) OR
(((tblAMUWork.CompletedDate1) Is Not Null)) OR (((tblAMUWork.CompletedDate2)
Is Not Null)) OR (((tblAMUWork.CompletedDate3) Is Not Null));


This query works fine except I don't want to see the information if the
variance is = to 0. I need to see if it is greater than or less than 0. Can
some please advise what criteria I need to enter in the query so that records
that contain 0 are not shown.

SELECT ID, DateRcd, TypeOfWork,
MISNumber, RptName, AmtRcd,
Nz([CompletedAmt],0)+Nz([CompletedAmt1],0)
+Nz([CompletedAmt2],0)+Nz([CompletedAmt3],0) AS TotalAmtCompleted,
Nz([AmtRcd],0)-Nz([TotalAmtCompleted],0) AS Variance,
WorkNotCompleted
FROM tblAMUWork
WHERE (CompletedDate Is Not Null
OR CompletedDate1 Is Not Null
OR CompletedDate2 Is Not Null
OR CompletedDate3 Is Not Null)
AND Nz([AmtRcd],0)-Nz([TotalAmtCompleted],0) <> 0

 

[Guest]

You can't generally use a derived column in a criteria expression. Try use
the actual expression for TotalAmtCompleted in the where clause like:

SELECT tblAMUWork.ID, tblAMUWork.DateRcd, tblAMUWork.TypeOfWork,
tblAMUWork.MISNumber, tblAMUWork.RptName, tblAMUWork.AmtRcd,
Nz([CompletedAmt],0)+Nz([CompletedAmt1],0)+Nz([CompletedAmt2],0)+Nz([CompletedAmt3],0)
AS TotalAmtCompleted, Nz([AmtRcd],0)-Nz([TotalAmtCompleted],0) AS Variance,
tblAMUWork.WorkNotCompleted
FROM tblAMUWork
WHERE (tblAMUWork.CompletedDate Is Not Null OR
tblAMUWork.CompletedDate1 Is Not Null OR tblAMUWork.CompletedDate2
Is Not Null OR tblAMUWork.CompletedDate3 Is Not Null)
AND
(Nz([AmtRcd],0)-Nz([CompletedAmt],0)+Nz([CompletedAmt1],0)+Nz([CompletedAmt2],0)+Nz([CompletedAmt3],0))<>0;