<>0 is not working

[Access_Novice]

I have a calculated field:

FilterZero: [DifferenceHours]+[DifferenceAmt]

On the criteria line I entered <>0 then when I execute the query I get a the dialog box called Enter a Parameter value with the field name of DifferenceHours as the name of the field.



The field called DifferenceHours is a calculated field, so I have a calculated field referencing another calculated field.

When I remove the <>0 criteria, the query executes fine.

I must be overlooking something. Any ideas?

[ItsMe]

Calculated Fields in tables, aka Calculated data type, have limitations. I do not have much experience with them. However, I have heard stories similar to yours before. There is a sequence to how data is retrieved and expressions are evaluated. Basically, you are trying to do too much at the exact same time. The best would probably be to use your calc expression in a query. Retrieve the calculation in a small query that takes a separate trip to the data and run that first. Use that small query as a subquery and apply where criteria in the main query.

Another approach may be to apply criteria to the other fields to handle Nulls or 0 or ...

[Access_Novice]

Calculated Fields in tables, aka Calculated data type, have limitations. I do not have much experience with them. However, I have heard stories similar to yours before. There is a sequence to how data is retrieved and expressions are evaluated. Basically, you are trying to do too much at the exact same time. The best would probably be to use your calc expression in a query. Retrieve the calculation in a small query that takes a separate trip to the data and run that first. Use that small query as a subquery and apply where criteria in the main query.

Another approach may be to apply criteria to the other fields to handle Nulls or 0 or ...

My calculated field is in a query, not a table. I'm fairly certain that I have filtered numbers in a calculated field before. I can't image that Access can't do this. Surely I am overlooking something.

[June7]

Post the entire query SQL statement for analysis.

[JamesDeckert]

If I understand, you have both [DifferenceHours] and [DifferenceAmt] being calculated in the query.
If this is so I think you have to repeat the calculation instead of using the new field name.
e.g. FilterZero: [field1]*2+[field2]/3

[Access_Novice]

Post the entire query SQL statement for analysis.

Here is my code. When I tried to run this code, I could only go into SQL view. Last night I might have removed the <>0 criteria but I can't remember.


[CODE][SELECT qryUnanetSummaryLockheedWeekEnding.FullName, qryUnanetSummaryLockheedWeekEnding.ProjectName, qryUnanetSummaryLockheedWeekEnding.TaskName, qryUnanetSummaryLockheedWeekEnding.VIPno, qryUnanetSummaryLockheedWeekEnding.ExostarLineNo, qryUnanetSummaryLockheedWeekEnding.LaborCat, qryUnanetSummaryLockheedWeekEnding.LockheedWE, Sum(Sum(qryUnanetSummaryLockheedWeekEnding.UnanetH ours)) AS UnanetHours, Sum(qryLockheedPaidSummary.PaidHours) AS PaidHours, qryUnanetSummaryLockheedWeekEnding.UnanetBillingRa te, qryLockheedPaidSummary.ExostarBillingRate, Sum(qryUnanetSummaryLockheedWeekEnding.InvoiceAmt) AS UnanetRev, Sum(qryLockheedPaidSummary.ExostarInvoice) AS LockheedInvoice
FROM qryUnanetSummaryLockheedWeekEnding INNER JOIN qryLockheedPaidSummary ON (qryUnanetSummaryLockheedWeekEnding.LockheedWE = qryLockheedPaidSummary.WeekEnding) AND (qryUnanetSummaryLockheedWeekEnding.ExostarLineNo = qryLockheedPaidSummary.Line) AND (qryUnanetSummaryLockheedWeekEnding.VIPno = qryLockheedPaidSummary.VIPno) AND (qryUnanetSummaryLockheedWeekEnding.BillingAccount No = qryLockheedPaidSummary.BillingAcct)
GROUP BY qryUnanetSummaryLockheedWeekEnding.FullName, qryUnanetSummaryLockheedWeekEnding.ProjectName, qryUnanetSummaryLockheedWeekEnding.TaskName, qryUnanetSummaryLockheedWeekEnding.VIPno, qryUnanetSummaryLockheedWeekEnding.ExostarLineNo, qryUnanetSummaryLockheedWeekEnding.LaborCat, qryUnanetSummaryLockheedWeekEnding.LockheedWE, qryUnanetSummaryLockheedWeekEnding.UnanetBillingRa te, qryLockheedPaidSummary.ExostarBillingRate;
/CODE]

[ItsMe]

Maybe someone else can spot a problem but I do not see any aggregate calcs that would cause a problem. Maybe you cannot get into design view because of the Group By Statement. Maybe there is a missing column. You might try removing the group by to get into design view. Then you can rebuild from there.

[Access_Novice]

Maybe someone else can spot a problem but I do not see any aggregate calcs that would cause a problem. Maybe you cannot get into design view because of the Group By Statement. Maybe there is a missing column. You might try removing the group by to get into design view. Then you can rebuild from there.

I tried your suggestion. I got the error message in the attached Word doc.

[June7]

Not even seeing the fields from the calc in that query.

Rather explicit error message. Why do you have Sum(Sum()):

Sum(Sum(qryUnanetSummaryLockheedWeekEnding.UnanetH ours))

[Access_Novice]

Not even seeing the fields from the calc in that query.

Rather explicit error message. Why do you have Sum(Sum()):

Sum(Sum(qryUnanetSummaryLockheedWeekEnding.UnanetH ours))

I had to recreate the query. Here is the code:

SELECT qryUnanetSummaryByLockheedWeekEnding.LockheedWE, qryUnanetSummaryByLockheedWeekEnding.EOMonth, qryUnanetSummaryByLockheedWeekEnding.Company, qryUnanetSummaryByLockheedWeekEnding.EmployeeType, qryUnanetSummaryByLockheedWeekEnding.FullName, qryUnanetSummaryByLockheedWeekEnding.ProjectName, qryUnanetSummaryByLockheedWeekEnding.TaskName, qryUnanetSummaryByLockheedWeekEnding.VIPno, qryUnanetSummaryByLockheedWeekEnding.ExostarLineNo, qryUnanetSummaryByLockheedWeekEnding.LaborCat, qryUnanetSummaryByLockheedWeekEnding.UnanetHours, qryLockheedPaidSummary.PaidHours, qryUnanetSummaryByLockheedWeekEnding.UnanetBillingRate, qryLockheedPaidSummary.ExostarBillingRate, qryUnanetSummaryByLockheedWeekEnding.UnanetInvoiceAmt, qryLockheedPaidSummary.ExostarInvoice, [UnanetHours]-[PaidHours] AS DifferenceHours, [UnanetInvoiceAmt]-[ExostarInvoice] AS DifferenceInvoice, [DifferenceHours]+[DifferenceInvoice] AS FilterZero
FROM qryUnanetSummaryByLockheedWeekEnding INNER JOIN qryLockheedPaidSummary ON (qryUnanetSummaryByLockheedWeekEnding.ExostarLineNo = qryLockheedPaidSummary.Line) AND (qryUnanetSummaryByLockheedWeekEnding.VIPno = qryLockheedPaidSummary.VIPno) AND (qryUnanetSummaryByLockheedWeekEnding.LockheedWE = qryLockheedPaidSummary.WeekEnding) AND (qryUnanetSummaryByLockheedWeekEnding.BillingAccountNo = qryLockheedPaidSummary.BillingAcct)
WHERE ((([DifferenceHours]+[DifferenceInvoice])<>0));

[ItsMe]

The following is evaluated before the SELECT statement. So those sums are not available yet.

WHERE ((([DifferenceHours]+[DifferenceInvoice])<>0));

Also, I believe and as mentioned in post #5, you cannot use [DifferenceHours]+[DifferenceInvoice] in the SELECT statement.

[June7]

Build aggregate query object without the WHERE clause. Now use that query as the source for another query (or report) and apply filter criteria there.

[Access_Novice]

The following is evaluated before the SELECT statement. So those sums are not available yet.

WHERE ((([DifferenceHours]+[DifferenceInvoice])<>0));

Also, I believe and as mentioned in post #5, you cannot use [DifferenceHours]+[DifferenceInvoice] in the SELECT statement.

Ok. Now I understand. You can't invoke a calculated field in the SELECT statement because the calculated field does not even exist in the table.

[Access_Novice]

So now that I fixed my calculated field problem, I am trying to do <>0 under DifferenceHours: [SumOfUnanetHours]-[PaidHours] and DifferenceInvoiceAmt: [UnanetInvoiceAmt]-[ExostarInvoice], where both columns have a 0.

While the results do filter out zeros, they are also filtering out blanks. But I want to keep values and blanks. How can I get this to filter only zeros?

[ItsMe]

You have several issues. You have the issue with your SELECT statement and you have your issue with the WHERE clause. Post #12 illustrates the appropriate way to address the WHERE clause.

In other words, you need to execute a query that retrieves data and calculates a sum before you can look for a sum. Create a few queries. Create one query and add stuff you need until it breaks. When it breaks, move back one step and use that as a subquery or a main query.