Unanswered: Got a strange error message when trying to do counters in a query.
I'm trying to write a query for a report and one of the attributes of this report requires to have a counter of how many records there are per category. This is the SQL i got so far:
SELECT KE0G_Drawings.DwgTag, KE0G_Drawings.DwgSht, KE0G_Drawings.DwgTitle1, KE0G_Drawings.DwgRevInt, KE0G_Drawings.DwgIssue, KE0G_Drawings.DwgType, KE0G_Drawings.DwgCommnt,
DateDiff("y",[KE0G_Drawings].[dwgIssue],Now()) AS DaysDifference, KE00_Disciplines.Description, KE0G_Drawings.DwgDiscipline,
IIf([DaysDifference]<14,"Less Than 2 Weeks","More Than 2 Weeks") AS age,
Sum(IIf(([DaysDifference] Is Not Null) OR ([DaysDifference]<14),1,0)) AS ageCounter
INNER JOIN KE0G_Drawings ON KE00_Disciplines.DisciplineCode = KE0G_Drawings.DwgDiscipline
WHERE (((KE0G_Drawings.DwgRevInt) Like "p%") AND ((KE0G_Drawings.DwgIssue) Is Not Null))
ORDER BY DateDiff("y",[KE0G_Drawings].[dwgIssue],Now()) DESC;
I resently put in the Sum counter I called ageCounter. This would be responsible for counting one of the categories. There are only 2 categories in this report; More than and Less Than.
Without that Sum counter the query works fine. As soon as I put it in I get this:
"You tried to execute a query that does not include the specified expression 'DwgTag' as part of an aggregate function"
This looks like a logic problem and I have my suspicions it is because I try to count from a 'counter' field, not from the table. But I see no other way of counting those records than by filtering the days.
The end result will (or should) be a field that counts all records less than 14 days and prints the sum. Then another that counts more than or equal to 14 days. Then another expression that combines those 2 fields into one and displays it per category.
So if anyone knows how to do this better, or knows how to go around that error, please help.