Error 3122 You tried to execute a query that does not include

[Steven Cheng]

I got this error message while trying to execute a query.

The query is a based upon a merger of a crosstab query and a table from the
SQL server. The error occurs in my subquery statement to create a running
balance.

The specified expression the it has a problem with was "" as part of the
aggregate function.

The subquery went something like this:

MTDHRs: (Select sum(Dupe.Reghours) FROM qryAllHours AS Dupe WHERE
(Dupe.BatchID between [Starting] AND [Ending]) AND (Dupe.[Dept]
=qryAllHours.[Dept]) AND (Dupe.[Job] = qryAllHours.[Job]) AND (Dupe.BatchID<=
qryAllHours.[BatchID]))

I am trying to generate a running total from qryAllhours by batchID, by
Dept, and by Job. The error occurs with the two WHERE conditions for the
Dept and Job. The query otherwise runs fine without them.

The exact error message goes:

You tried to execute a query that does not include the specified expression
'' as part of an aggregate function.

Any ideas where to start. The crosstab table is fine and there is always a
value for Dept AND for Job.

 

[John Vinson]

MTDHRs: (Select sum(Dupe.Reghours) FROM qryAllHours AS Dupe WHERE
(Dupe.BatchID between [Starting] AND [Ending]) AND (Dupe.[Dept]
=qryAllHours.[Dept]) AND (Dupe.[Job] = qryAllHours.[Job]) AND (Dupe.BatchID<=
qryAllHours.[BatchID]))

I am trying to generate a running total from qryAllhours by batchID, by
Dept, and by Job. The error occurs with the two WHERE conditions for the
Dept and Job. The query otherwise runs fine without them.

The exact error message goes:

You tried to execute a query that does not include the specified expression
'' as part of an aggregate function.

Any ideas where to start. The crosstab table is fine and there is always a
value for Dept AND for Job.

You would get this error if qryAllHours.[Job] or qryAllHoure.[BatchID]
were NULL. I presume that they exist in the query of which this
subquery is a part?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Steven Cheng]

John;

Thanks for that note.

There should be a null value as the fields are calculated fields to ensure
that there isn't a value:

Job: =iif([TempJobCode]="",[HomeJobCode],[TempDepartmentCode]).

This is the function that I used to ensure that the Job code was not null.
And Yes, they do exist.

I have used a Make Table query to create this table and then use the
subquery calculation on it to find it works. Very strange.

MTDHRs: (Select sum(Dupe.Reghours) FROM qryAllHours AS Dupe WHERE
(Dupe.BatchID between [Starting] AND [Ending]) AND (Dupe.[Dept]
=qryAllHours.[Dept]) AND (Dupe.[Job] = qryAllHours.[Job]) AND (Dupe.BatchID<=
qryAllHours.[BatchID]))

I am trying to generate a running total from qryAllhours by batchID, by
Dept, and by Job. The error occurs with the two WHERE conditions for the
Dept and Job. The query otherwise runs fine without them.

The exact error message goes:

You tried to execute a query that does not include the specified expression
'' as part of an aggregate function.

Any ideas where to start. The crosstab table is fine and there is always a
value for Dept AND for Job.

You would get this error if qryAllHours.[Job] or qryAllHoure.[BatchID]
were NULL. I presume that they exist in the query of which this
subquery is a part?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

There should be a null value as the fields are calculated fields to ensure
that there isn't a value:

Job: =iif([TempJobCode]="",[HomeJobCode],[TempDepartmentCode]).

A zero length string is NOT the same as NULL. The string constant ""
is a specific, defined value - a string of zero length. NULL, on the
other hand, means "this field is undefined, unknown, it could be
anything".

Try

Job: IIF(IsNull([TempJobCode]), ,[HomeJobCode], [TempDepartmentCode])


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps