Calculated fields as criteria

[raugust]

Hi:

I've created a query that finds the last non-zero field in a series of fields and captures the year and amount in calculated fields. I've defined the fields in the calcs by wrapping the expressions in CDbl(). When I try to use the calc fields as select criteria, however, my calculations break down, asking for input that is existing data. Whast am I doing wrong, do you think?

RA

 

[raugust]

Figured this one out myself, after lots of research - sub-queries don't work, but if I create a second query based on the one with the calc fields and pass the fields to the second query, the select criteria work fine in the second query.

 

[cb@chrisb.eu]

Hmm. Doesn't work for me. Query 1: Age calculated from DateOfBirth. No sub-queries, so Query 2 [Age] and [AgeCount]. Works fine: 73 1-year olds, 92 2-year olds, etc. Now I need to put this into categories, which is where it falls down. New column: AgeCount1-10:[AgeCount],Count, Criteria: Between 0 and 10. All I get is a column of 1s. If I set this as 1-10:[Age], same result. what am I doing wrong?
SQL:SELECT [PD AgeCount].Age, [PD AgeCount].AgeCount, Count([PD AgeCount].AgeCount) AS [AgeCount1-10]
FROM [PD AgeCount]
GROUP BY [PD AgeCount].Age, [PD AgeCount].AgeCount
HAVING (((Count([PD AgeCount].AgeCount)) Between 0 And 10));