Prev: samira
Next: Albert Kallal
From: ddoblank on
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.
From: John W. Vinson on
On Thu, 22 Apr 2010 17:56:01 -0700, ddoblank
<ddoblank(a)discussions.microsoft.com> wrote:

>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 [MVP]
From: ddoblank on
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.


"John W. Vinson" wrote:

> On Thu, 22 Apr 2010 17:56:01 -0700, ddoblank
> <ddoblank(a)discussions.microsoft.com> wrote:
>
> >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 [MVP]
> .
>
From: John W. Vinson on
On Fri, 23 Apr 2010 17:32:01 -0700, ddoblank
<ddoblank(a)discussions.microsoft.com> wrote:

>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 [MVP]
From: ddoblank on
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

"John W. Vinson" wrote:

> On Fri, 23 Apr 2010 17:32:01 -0700, ddoblank
> <ddoblank(a)discussions.microsoft.com> wrote:
>
> >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 [MVP]
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: samira
Next: Albert Kallal