Help with Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following query:

SELECT tblAMUWork.AMUWrkID, 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;

When I run the query the only records that I want to see are the records
that have a variance. If the number is 0 then I don't want to see it. When
I try to add that criteria I get a data mismatch.

Does anyone have any ideas as to what I am doing wrong?
 
You need to redo the entire calculation in the where clause. TRY

SELECT tblAMUWork.AMUWrkID, 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 Nz(AmtRcd,0) -
Nz([CompletedAmt],0)+Nz([CompletedAmt1],0)+Nz([CompletedAmt2],0)+Nz([CompletedAmt3],0)
<> 0




--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
What did you try as a criteria?

Try
SELECT tblAMUWork.AMUWrkID, 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 Nz([AmtRcd],0)-Nz([TotalAmtCompleted],0) <> 0
 
I tried using the <>0 in the variance column. I also tried the suggestions
below and both of them still showed the records that had 0's. Then it would
tell me that I had a data mismatch and everything in the results changed to
#Name?

Ofer Cohen said:
What did you try as a criteria?

Try
SELECT tblAMUWork.AMUWrkID, 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 Nz([AmtRcd],0)-Nz([TotalAmtCompleted],0) <> 0


--
Good Luck
BS"D


Welthey said:
I have the following query:

SELECT tblAMUWork.AMUWrkID, 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;

When I run the query the only records that I want to see are the records
that have a variance. If the number is 0 then I don't want to see it. When
I try to add that criteria I get a data mismatch.

Does anyone have any ideas as to what I am doing wrong?
Click to expand...
Click to expand...
 
Back
Top