Invalid use of null in query expression

[Jon]

I'm trying to execute an aggregate function as part of a select
statement. It requires me to include all columns not in aggregate
function in the 'Group By' clause. That makes sense.

However, one of the columns may have null values because it's based on
a left join.

So I use the NZ() function to return 0 if that's the case. I then
return that value, whatever it is as [Exam Total]. If I don't I get
the 'Invalid use of null in query expression' error.

However, as it stands now, I'm getting an error like "You tried to
execute a query that does not include the specified expression <name>
as part of an aggregate function. (Error 3122)".

So if I correct one error, I get the other. Here's the query... any
ideas? - jon


SELECT [Level Ordered], [Customer to Level].[Customer #],
SUM([Customer to Level].[Quantity]) AS [Level Count],
NZ([Total], 0) as [Exam Total]
FROM [Customer to Level] LEFT JOIN
[Count Exams Per Customer]
ON ([Customer to Level].[Customer #]=[Count Exams Per Customer].
[Customer Number]) AND ([Customer to Level].[Level Ordered]=[Count
Exams Per Customer].[This Level])
WHERE [Customer to Level].[Order Date] < (SELECT [Actual Date] FROM
Dates WHERE [Date Name] = 'Billing Period Start' )
GROUP BY [Customer to Level].[Level Ordered], [Customer #], [Exam
Total];

 

[Wolfgang Kais]

Hello Jon.

I'm trying to execute an aggregate function as part of a select
statement. It requires me to include all columns not in aggregate
function in the 'Group By' clause. That makes sense.

However, one of the columns may have null values because it's based
on a left join.

So I use the NZ() function to return 0 if that's the case. I then
return that value, whatever it is as [Exam Total]. If I don't I get
the 'Invalid use of null in query expression' error.

However, as it stands now, I'm getting an error like "You tried to
execute a query that does not include the specified expression <name>
as part of an aggregate function. (Error 3122)".

So if I correct one error, I get the other. Here's the query... any
ideas? - jon


SELECT [Level Ordered], [Customer to Level].[Customer #],
SUM([Customer to Level].[Quantity]) AS [Level Count],
NZ([Total], 0) as [Exam Total]
FROM [Customer to Level] LEFT JOIN
[Count Exams Per Customer]
ON ([Customer to Level].[Customer #]=[Count Exams Per Customer].
[Customer Number]) AND ([Customer to Level].[Level Ordered]=[Count
Exams Per Customer].[This Level])
WHERE [Customer to Level].[Order Date] < (SELECT [Actual Date] FROM
Dates WHERE [Date Name] = 'Billing Period Start' )
GROUP BY [Customer to Level].[Level Ordered], [Customer #], [Exam
Total];

Try NZ([Total], 0) instead if [Exam Total] in the GROUP BY clause.

 

[Jon]

That's exactly what I thought, Wolfgang. But if I Group By
NZ([Total], 0), with or without aliasing, by the way, I get the
following error...
'Invalid use of null'
It seems like a catch-22. If I correct one error, it produces the
other.

 

[Wolfgang Kais]

Hello Jon.

That's exactly what I thought, Wolfgang. But if I Group By
NZ([Total], 0), with or without aliasing, by the way, I get the
following error...
'Invalid use of null'
It seems like a catch-22. If I correct one error, it produces
the other.

What happens if you execute the query without the where clause?
Is there an [Actual Date] where [Date Name] = 'Billing Period Start'?