Please help me understand why I get the following error message when trying to execute a query using this expression:

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

EXPRESSION
Expr1: (SELECT [Daily Goal] FROM [DailyProductionGoal] WHERE [qryStreamFactor].[ProductionMonth]=[DailyProductionGoal].[ProductionMonth] AND [qryStreamFactor].[ProductionYear]=[DailyProductionGoal].[ProductionYear])

Thanks for your help.

you have an aggregrate function in your query somewhere, although I don't see one in the example you provided. You need to group the fields that are not aggregates. Here is a FAQ that explains why this happens, and what you can do about it:

http://www.access-programmers.co.uk/forums/showthread.php?t=135763

Show us your whole query, please (pasted from SQL view).

Here is the query:

SELECT qryStreamFactor.[Production Month], qryStreamFactor.ProductionYear, qryStreamFactor.Date, Sum(qryStreamFactor.PackedTubes) AS SumOfPackedTubes, Sum(qryStreamFactor.SealedProduction) AS SumOfSealedProduction, (SELECT [Daily Goal] FROM [DailyProductionGoal] WHERE [qryStreamFactor].[ProductionMonth]=[DailyProductionGoal].[ProductionMonth] AND [qryStreamFactor].[ProductionYear]=[DailyProductionGoal].[ProductionYear]) AS Expr1
FROM qryStreamFactor, DailyProductionGoal
GROUP BY qryStreamFactor.[Production Month], qryStreamFactor.ProductionYear, qryStreamFactor.Date, qryStreamFactor.Date
HAVING (((qryStreamFactor.Date) Between [Forms]![Report Selection]![FromDate] And [Forms]![Report Selection]![ToDate]));

Looks like you need to group by "Expr1", too.

Tried that and it says syntax error. in query expression.

What does the quote mean in the error? I have never seen that before.

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

it doesn't mean anything. It looks like a truncated message. That's all.

Try this as a substitute to what you're trying to use now:SELECT qryStreamFactor.[Production Month], qryStreamFactor.ProductionYear,
qryStreamFactor.Date, Sum(qryStreamFactor.PackedTubes) AS SumOfPackedTubes,
Sum(qryStreamFactor.SealedProduction) AS SumOfSealedProduction,
DLOOKUP("[Daily Goal]", "DailyProductionGoal", "[ProductionMonth] =
[ProductionMonth] AND [ProductionYear] = [ProductionYear]") AS Expr1

FROM qryStreamFactor

GROUP BY qryStreamFactor.[Production Month], qryStreamFactor.ProductionYear,
qryStreamFactor.Date, qryStreamFactor.Date

WHERE (((qryStreamFactor.Date) Between [Forms]![Report Selection]![FromDate]
And [Forms]![Report Selection]![ToDate]));

This did not work and I am sure it is through no error of yours. What I am trying to do is this: Each month the Daily goal changes so I have a table named ProductionDailyGoal that has 3 fields, DailyGoal, ProductionMonth and ProductionYear. This information is fed to the table from a form that I want the manager to update each month with the new goal. I want the above query to look at the ProductionMonth and ProductionYear fields and pull the applicable DailyGoal, i.e. daily goal for 1/1/2008 through 1/31/2008 is 543,478, for 2/1/2007 through 2/28/2007 is 510,000. Can you help me understand where I am going wrong with this query?

I'm pretty sure it's a data type mismatch error. Any possibility you could upload the database? Is it the same one that's in your other thread about the relationships? The description is a bit vague. But then again, maybe it's just simply too complex to understand. Can you upload the file and point me in the right direction? I bet we could get it tweaked in a few short seconds if you could give us the real thing. :)

I repaired the data type mismatch error. It is only giving me the results of the first row of data in the ProductionDailyGoal table which is the daily goal for January 2007. I tried adding a where statement and it still gives me only that row of data. Here is a copy of the previously mentioned database with the new form and queries. The query is named TestMTDOverallLMB.

Here is the correct SQL to useSELECT qryStreamFactor.[Production Month], qryStreamFactor.ProductionYear,
qryStreamFactor.Date, Sum(qryStreamFactor.PackedTubes) AS SumOfPackedTubes,
Sum(qryStreamFactor.SealedProduction) AS SumOfSealedProduction,
DLookUp("[Daily Goal]","DailyProductionGoal","[productionyear] = " &
[productionyear] & " AND [productionmonth] = " & [production month]) AS Goal

FROM qryStreamFactor

GROUP BY qryStreamFactor.[Production Month],
qryStreamFactor.ProductionYear, qryStreamFactor.Date

HAVING (((qryStreamFactor.Date) Between [Forms]![Report Selection]![FromDate]
And [Forms]![Report Selection]![ToDate]));There were a few errors in the statement you were working with. Nothing drastic, but almost impossible to fix without actually seeing the query in it's entirety. ;) It should work fine now...

ajetrumpet, this worked! Thank you. I have one question. . . It is reading the result as text and I need it to read it as a number so that I can calculate on the field in my report. How do I change it?

I have no idea. If your talking about the month or year fields, then the data type is whatever data type you have in the original table from which the data was pulled. In this case though, you are pulling from a query, so you will have to find the source table of the data from that query (or the one beneath it, if you have stacked the queries (I can't remember)). If it is by chance text in that table field, change it like this (this is the "month" field as an example):SELECT clng(qryStreamFactor.[Production Month])They are called conversion functions. The help menu has every conversion function there is in the program.ajetrumpet, this worked!Of course it worked, I tested it before I posted it. :D

I'm sorry, I am referring to the daily goal. In the DailyProductionGoal table the daily goal is set to number, however when the query looks up the field it brings back the correct number but as text. I hope that makes sense.

You can tell by the alignment I suppose. Is it aligned to the left now? Try a conversion function on it, like I had said before... clng(DLookUp("[Daily Goal]","DailyProductionGoal","[productionyear] = " &
[productionyear] & " AND [productionmonth] = " & [production month])) AS Goal

I looked at all of the data types of the fields in the DLookup expression and the month and year fields in the qryStreamFactor are derived from a Date/Time data type and the month and year fields from the DailyProductionGoal table are set to number. I tried the code above suggested by ajetrumpet and it still returns the number as text instead of numeric. Can someone help me get the Goal field result to be numeric instead of text?

ajetrumpet, our posts must have overlapped. My apologies. It worked (as you knew it would). Thanks again for all of your help on this one.

It worked when I tried it lmg...

I think we might be on the wrong page here. As I have typed it to you, the number is returned as a "long" data type when the query is run. Are you trying to accomplish this on a report instead? I thought you had mentioned that before...

ajetrumpet, our posts must have overlapped. My apologies. It worked (as you knew it would). Thanks again for all of your help on this one.Not a problem. :) I've had overlapping posts my entire life around here. :p