Should/How My Aggregate Function be Referenced? 1

[Mike555]

When running the below query I receive the error message:
"You tried to execute a query that does not include the specified expression <name> as part of an aggregate function"

I believe the error may be due to an incorrect syntax in the GROUP BY clause, but anything I use doesn't seem to work. Could anyone please help me understand what's wrong with this query? Thanks.

SELECT IIF(Station1 Is Not Null,Format((Date()+(Sum(Station1)/1964.28)),"Short Date"),"NOT APPLICABLE") AS OutToDateStation1
FROM tblSQFTByStation INNER JOIN tblOrder ON tblSQFTByStation.OrderID = tblOrder.OrderID
WHERE (Station1 Is Not Null)
GROUP BY 1;

--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[PHV]

Why not simply this ?
SELECT Format((Date()+(Sum(Station1)/1964.28)),"Short Date") AS OutToDateStation1
FROM tblSQFTByStation INNER JOIN tblOrder ON tblSQFTByStation.OrderID = tblOrder.OrderID
WHERE Station1 Is Not Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jebry]

Hi!

Try this:

SELECT IIF(Station1 Is Not Null,Format((Date()+(Sum(Station1)/1964.28)),"Short Date"),"NOT APPLICABLE") AS OutToDateStation1
FROM tblSQFTByStation INNER JOIN tblOrder ON tblSQFTByStation.OrderID = tblOrder.OrderID
WHERE (Station1 Is Not Null)
GROUP BY IIF(Station1 Is Not Null,Format((Date()+(Sum(Station1)/1964.28)),"Short Date"),"NOT APPLICABLE");

This code assumes that you want to only see each date, or NOT APPLICABLE, only once.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[Mike555]

PHV, Good Point - Why perform the same thing twice? Thanks for the suggestion.

Jeff, Thanks for your help also!


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.