Aggregate function frustrations

[JJSRich]

I have used this line elsewhere, made a change to how we track the value

 IIf([q].[value]=0,Sum([h].[built]*[h].[value]),[q].[value])

I have a query that grabbs address details, orders, values, etc, grouping by the address. When trying to use the above expression, instead of just value Access complains that I cannot have aggregate function in GROUP BY clause, but when I select "Expression" as its sort criteria, Access tells me that I tried to execute a query that doesn't include the above expression in an aggregate function. What am I supposed to choose as its sort criteria?

I can't win for losing!

 

[r937]

pls show entire query

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[genomon]

If you are trying to use the above expression as a returned field, you need to alias it using AS.

Illegitimi non carborundum.

 

[JJSRich]

o.. k. I found a deeper problem and a workaround. There's a flaw in the ODBC 3.51 connector I was using. Working around that, (using the 5.1 connector) and creating views has fixed the above issue. Thanks anyhow.