Results 1 to 5 of 5
  1. #1
    fooforever is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    3

    Query Error 3071

    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:
    Code:
    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.

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    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)

  3. #3
    fooforever is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    3
    Thanks I kinda thought as much how would I implement the DSUM() functions ?

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    take a look at the syntax of that function in the help menu.

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

  5. #5
    fooforever is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Posts
    3
    Thanks simplifying thing helped

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-31-2011, 11:07 AM
  2. Help with Error (3071)
    By ron3rbns in forum Access
    Replies: 1
    Last Post: 03-05-2011, 01:56 AM
  3. Need help with Error (3071)
    By ron3rbns in forum Queries
    Replies: 0
    Last Post: 03-03-2011, 10:35 AM
  4. error 3071 on OpenReport command
    By degras in forum Reports
    Replies: 5
    Last Post: 02-15-2011, 10:40 AM
  5. Microsoft Access Error 3071
    By Ev0luTioN in forum Access
    Replies: 1
    Last Post: 08-04-2010, 04:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums