Query Error 3071

[fooforever]

Hi,
Im creating a database for a friends homemade cake business and have hit some trouble with some of my queries. I have 3 in the databse and i get the 3071 error on two, the other works fine.

The error is:


This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

My SQL for one of the queries is:

PARAMETERS [Please enter end date] DateTime;
SELECT Cakes.Flour, Cakes.Eggs, Cakes.Milk, Cakes.Butter, Cakes.Sugar, Cakes.[Cake ID] AS [Cakes_Cake ID], Cakes.[Other Indredients], Orders.[Order Number], Orders.[Cake ID] AS [Orders_Cake ID], Orders.[Delivery Date], Sum(Cakes.Flour) AS SumOfFlour, Sum(Cakes.Eggs) AS SumOfEggs, Sum(Cakes.Milk) AS SumOfMilk, Sum(Cakes.Butter) AS SumOfButter, Sum(Cakes.Sugar) AS SumOfSugar
FROM Cakes INNER JOIN Orders ON Cakes.[Cake ID] = Orders.[Cake ID]
GROUP BY Cakes.Flour, Cakes.Eggs, Cakes.Milk, Cakes.Butter, Cakes.Sugar, Cakes.[Cake ID], Cakes.[Other Indredients], Orders.[Order Number], Orders.[Cake ID], Orders.[Delivery Date]
HAVING ((Orders.[Delivery Date]) Between Date() And [Please enter end date]);

The user is required to enter the date on running the query and it searches between the current date and the entered date.

Im sure I've had these working before but they seemed to have suddenly stopped working, any ideas would be appreciated.

[ajetrumpet]

my guess that the error is being thrown because of all the SUM() functions you have in there. More or less, this message is saying exactly what you get out of it. Your query is too overwhelming for the program.

change it. how about DSUM()'s with criterias. That to me, shouldn't be needed either, but it would eliminate the need for the complex GROUP BY clause.

(rarely is it anything else with 3071's)

[fooforever]

Thanks I kinda thought as much how would I implement the DSUM() functions ?

[ajetrumpet]

take a look at the syntax of that function in the help menu.

If you cannot follow that, post back with ?'s.

[fooforever]

Thanks simplifying thing helped