aggregate function

[ddoblank]

I have been running a series of queries now for about a year and a half with
no problems, that I know of anyways. Today, I found out that a job didn't
show up in a report that I run daily and I can not figure out why. I took a
look in one query and the job is there, I take a look in the next query that
runs off that one and it is gone, so I entered a filter to see if I could
just pull that one job out and I got this error message.

You tried to execute a query that does not include the specified expression
‘qryJrun_Step_5.[Kitting Job #] Like “27489765-001†as part of an aggregate
function.

Any ideas? The format for the column hasn't changed, the data for this job
is the same as every other job that is showing up.

 

[John W. Vinson]

I have been running a series of queries now for about a year and a half with
no problems, that I know of anyways. Today, I found out that a job didn't
show up in a report that I run daily and I can not figure out why. I took a
look in one query and the job is there, I take a look in the next query that
runs off that one and it is gone, so I entered a filter to see if I could
just pull that one job out and I got this error message.

You tried to execute a query that does not include the specified expression
‘qryJrun_Step_5.[Kitting Job #] Like “27489765-001” as part of an aggregate
function.

Any ideas? The format for the column hasn't changed, the data for this job
is the same as every other job that is showing up.

This is odd. It sounds like the query thinks you're using the expression

qryJrun_Step_5.[Kitting Job #] Like “27489765-001”

as the name of a field, which doesn't make much sense at all.

Did you introduce the "smart quotes" “” in place of "" in copying the message
to this forum? or are they in the query somewhere?

Perhaps you should open the entire query in SQL view and post it here.

 

[ddoblank]

Hi John,

I may have inadvertently typed in the wrong quotation marks on this request.
In the query itself, I just typed in the job number and tabbed out. The
program put in the quotation marks it normally uses. With that said, here is
the SQL statement from this query.

SELECT qryJrun_Step_5.[Part #], qryJrun_Step_5.DESC1, qryJrun_Step_5.[Dem
Qty], Sum(qryJrun_Step_5.[Prev Ordered]) AS [SumOfPrev Ordered],
qryJrun_Step_5.[Pref Vendor #], qryJrun_Step_5.[Kitting Job #]
FROM qryJrun_Step_5
GROUP BY qryJrun_Step_5.[Part #], qryJrun_Step_5.DESC1, qryJrun_Step_5.[Dem
Qty], qryJrun_Step_5.[Pref Vendor #], qryJrun_Step_5.[Kitting Job #]
HAVING (((qryJrun_Step_5.[Kitting Job #])="27489765-001"));

I don't normally have a job # filter running here. I just entered in this
query to see if it would pull this job out only. If I remove the filter, the
query runs fine and this job is in there.

I have been running a series of queries now for about a year and a half with
no problems, that I know of anyways. Today, I found out that a job didn't
show up in a report that I run daily and I can not figure out why. I took a
look in one query and the job is there, I take a look in the next query that
runs off that one and it is gone, so I entered a filter to see if I could
just pull that one job out and I got this error message.

You tried to execute a query that does not include the specified expression
‘qryJrun_Step_5.[Kitting Job #] Like “27489765-001†as part of an aggregate
function.

Any ideas? The format for the column hasn't changed, the data for this job
is the same as every other job that is showing up.

This is odd. It sounds like the query thinks you're using the expression

qryJrun_Step_5.[Kitting Job #] Like “27489765-001â€

as the name of a field, which doesn't make much sense at all.

Did you introduce the "smart quotes" Ҡin place of "" in copying the message
to this forum? or are they in the query somewhere?

Perhaps you should open the entire query in SQL view and post it here.

 

[John W. Vinson]

Hi John,

I may have inadvertently typed in the wrong quotation marks on this request.
In the query itself, I just typed in the job number and tabbed out. The
program put in the quotation marks it normally uses. With that said, here is
the SQL statement from this query.

Hrm. Not sure why it's getting that error, but try moving the criterion to the
WHERE clause:

SELECT qryJrun_Step_5.[Part #], qryJrun_Step_5.DESC1, qryJrun_Step_5.[Dem
Qty], Sum(qryJrun_Step_5.[Prev Ordered]) AS [SumOfPrev Ordered],
qryJrun_Step_5.[Pref Vendor #], qryJrun_Step_5.[Kitting Job #]
FROM qryJrun_Step_5
GROUP BY qryJrun_Step_5.[Part #], qryJrun_Step_5.DESC1, qryJrun_Step_5.[Dem
Qty], qryJrun_Step_5.[Pref Vendor #], qryJrun_Step_5.[Kitting Job #]
WHERE (((qryJrun_Step_5.[Kitting Job #])="27489765-001"));

 

[ddoblank]

The WHERE clause is not normally there. I just put it in to see if I could
see this one job. Normally this query does not have a WHERE clause of any
kind in it.

Maybe I am not explainging things clearly. The job number that I have in the
WHERE clause is in this query when I run it without, however, there is a
report that prints out that shows all of the jobs that I have to order for.
This particular job didn't show up in this report even though it is in the
query. So I put the WHERE clause in to see what would happen and that is when
I got the "aggregate function" error in my original message.

Does that help at all?
Thanks
Darren

Hi John,

I may have inadvertently typed in the wrong quotation marks on this request.
In the query itself, I just typed in the job number and tabbed out. The
program put in the quotation marks it normally uses. With that said, here is
the SQL statement from this query.

Hrm. Not sure why it's getting that error, but try moving the criterion to the
WHERE clause:

SELECT qryJrun_Step_5.[Part #], qryJrun_Step_5.DESC1, qryJrun_Step_5.[Dem
Qty], Sum(qryJrun_Step_5.[Prev Ordered]) AS [SumOfPrev Ordered],
qryJrun_Step_5.[Pref Vendor #], qryJrun_Step_5.[Kitting Job #]
FROM qryJrun_Step_5
GROUP BY qryJrun_Step_5.[Part #], qryJrun_Step_5.DESC1, qryJrun_Step_5.[Dem
Qty], qryJrun_Step_5.[Pref Vendor #], qryJrun_Step_5.[Kitting Job #]
WHERE (((qryJrun_Step_5.[Kitting Job #])="27489765-001"));

 

[John W. Vinson]

The WHERE clause is not normally there. I just put it in to see if I could
see this one job. Normally this query does not have a WHERE clause of any
kind in it.

Maybe I am not explainging things clearly. The job number that I have in the
WHERE clause is in this query when I run it without, however, there is a
report that prints out that shows all of the jobs that I have to order for.
This particular job didn't show up in this report even though it is in the
query. So I put the WHERE clause in to see what would happen and that is when
I got the "aggregate function" error in my original message.

Sounds like something's getting corrupt (or I may be missing something that
should be obvious).

Try copying and pasting the SQL of the query out to a text file (in Notepad
for example). Delete the query; compact and repair the database (the backend
too if it's split, there might be a damaged index). Recreate the query by
copying the SQL back into the SQL window of a new query. Any change?