Getting a prompt using IIf statement 1

[alanna101207]

I am not sure what I am doing wrong here. I have a query that is based on another query. I use this IIF statement:


FlexUsed: IIf([SumOfPoints Sum]>[DailyPoints],[SumOfPoints Sum]-[DailyPoints],0)

I get the results I am looking for, however, I get a pop-up prompt to enter a parameter value for [SumOfPoints Sum]

The actual name of the field is "Points Sum", but it comes from another Query, so it is the Sum of "Points Sum", but if I use the IIf statement like this I get no prompt, but it doesn't give me the correct results:

FlexUsed: IIf([Points Sum]>[DailyPoints],[Points Sum]-[DailyPoints],0)

Just wonder if anybody knows why this annoying prompt is popping up?

Here's the entire SQL View of my query if that helps:

SELECT DISTINCTROW qry_PointsByDay.ProgramID, qry_PointsByDay.[User Full Name], Format$(qry_PointsByDay.DailyDate,'Long Date') AS [DailyDate By Day], Sum(qry_PointsByDay.[Points Sum]) AS [SumOfPoints Sum], IIf([SumOfPoints Sum]>[DailyPoints],[SumOfPoints Sum]-[DailyPoints],0) AS FlexUsed, qry_PointsByDay.DailyPoints
FROM qry_PointsByDay
GROUP BY qry_PointsByDay.ProgramID, qry_PointsByDay.[User Full Name], Format$(qry_PointsByDay.DailyDate,'Long Date'), IIf([SumOfPoints Sum]>[DailyPoints],[SumOfPoints Sum]-[DailyPoints],0), qry_PointsByDay.DailyPoints;

 

[lespaul]

you can't use an alias (SumOfPoints Sum) in the same query, you have to use the formula:

SELECT DISTINCTROW qry_PointsByDay.ProgramID, qry_PointsByDay.[User Full Name], Format$(qry_PointsByDay.DailyDate,'Long Date') AS [DailyDate By Day], Sum(qry_PointsByDay.[Points Sum]) AS [SumOfPoints Sum], IIf([b]Sum(qry_PointsByDay.[Points Sum][/b])>[DailyPoints],[b]Sum(qry_PointsByDay.[Points Sum][/b])-[DailyPoints],0) AS FlexUsed, qry_PointsByDay.DailyPoints
FROM qry_PointsByDay
GROUP BY qry_PointsByDay.ProgramID, qry_PointsByDay.[User Full Name], Format$(qry_PointsByDay.DailyDate,'Long Date'), IIf([SumOfPoints Sum]>[DailyPoints],[SumOfPoints Sum]-[DailyPoints],0), qry_PointsByDay.DailyPoints;

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[alanna101207]

Okay, I tried that and am getting the following error:

"Cannot have aggregate function in GROUP BY clause

 

[PHV]

And what about this ?

SELECT ProgramID, [User Full Name], Format$(DailyDate,'Long Date') AS [DailyDate By Day]
, Sum([Points Sum]) AS [SumOfPoints Sum]
, IIf(Sum([Points Sum])>[DailyPoints],Sum([Points Sum])-[DailyPoints],0) AS FlexUsed
, DailyPoints
FROM qry_PointsByDay
GROUP BY ProgramID, [User Full Name], DailyDate, DailyPoints;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alanna101207]

Awe...I see...geez. "Expression" where I had "Group By"

Much thanks.