filtering calculated fields

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

Guest

I have a calculated field in a query which produces a null value for some
records and a number for other records. Now I want to filter the query to
only show me the certain records based on what the calculation produces, so I
added the criteria under the calculated field in the design view of the
query. However, when I go to the datasheet view of the query, I get prompts
for (some of) the fields which the calculation is using, as if it is a
parameter query. Why does it do this? Should I in fact be able to filter a
query on a calculated value? Thanks in advance. I did look amongst previous
posts but did not find an answer.
 
There are some cases where Access can perform calculations on a calculated
field, and some where it cannot.

If the query performs no aggregation, you should be able to place Criteria
under a calculated field. Access *should* repeat the entire calculation in
the WHERE clause of the query. Switch to SQL view (View menu) to see if this
is the case.

If the query performs any aggregation (e.g. GROUP BY clause), the WHERE is
applied before aggregation, whereas the HAVING is applied afterwards. If the
calculation depends on the aggregated values, then Access is not about to
use it in the WHERE clause, and will pop up the
parameter.

There are other factors, but that might get you started. BTW, you might want
to make sure that Access understands the data type of the calculated field
correctly. Details:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
 
Hi,

I've been having the exact same problem and the aggregate function would
appear to be the cause. Is there a work-around to this?

Regards

Matt
 
Not sure which function you are referring to.

Understanding the difference between the timing of the WHERE and the HAVING
is probaly the crucial issue.
 
Back
Top