Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2004
    Posts
    80

    Unanswered: PLEASE HELP! Validation rule for calculated queries?

    I have two calculated fields in a query, as shown below. What it is supposed to do is run the query to count the number of menu items ordered in the user-specified year (YearOrdered: Year([DateOrdered]) and display the year from which the menu item came (YearAdded: Year([DateAdded]). YearOrdered allows the user to select the year from which the orders were placed, whereas YearAdded displays the name of the year that the menu item was added and thus identify the menu it is from (changed annually).

    Problem is, the criterion I placed under YearAdded doesn't work. I need to ensure that, for whatever year is entered as a parameter criteria in YearOrdered (say, 2004), only menu items added to the menu in that year (2004) are displayed. Can anyone help me on this? The syntax I've used doesn't seem to work and I'm at my wit's end trying to find another solution.

    Thanks.
    Attached Thumbnails Attached Thumbnails yearexps.gif  
    Last edited by graceadair; 05-20-04 at 17:18.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try being explicit.

    ([YearAdded] >= Year([YearOrdered])

    The SQL should have a where clause that looks like this:

    SELECT *
    FROM tbl
    WHERE Year(DateOrdered) = [Enter the year ...] AND Year(DateAdded) >= Year(YearOrdered)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    Posts
    80

    Considers it a parameter query?

    hi Teddy,

    thanks for your reply.

    I tried this, but then it came up with this:

    And so I added an extra bracket at the end (assuming that's right) and got this when I tried to run the query:

    What to do? Did I put it in right?

    - Grace
    Attached Thumbnails Attached Thumbnails 1.gif   2.gif  

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You're missing a closing paranthesis after ([YearAdded]
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    May 2004
    Posts
    80

    Still parameter...

    Unfortunately, even when closing it with another bracket, Access comes up with a parameter query box for both YearAdded and YearOrdered when I try and run the query. Stupid thing.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try putting the where clause I gave you in the SQL view.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    May 2004
    Posts
    80

    Where in the SQL?

    Where exactly in the following SQL source would I place it (i.e what current code would I replace, and with which bit?) Can you insert it in red for me please? Sorry about all these extra questions, it's all just not working for me.

    SELECT Sum(tblMealOrder.Quantity) AS SumOfQuantity, tblMealOrder.MenuItemID, tblMenuItems.Name, tblMenuItems.Category, tblMenuItems.MenuYear
    FROM tblOrders INNER JOIN (tblMenuItems INNER JOIN tblMealOrder ON tblMenuItems.MenuItemID = tblMealOrder.MenuItemID) ON tblOrders.OrderID = tblMealOrder.OrderID
    GROUP BY tblMealOrder.MenuItemID, tblMenuItems.Name, tblMenuItems.Category, tblMenuItems.MenuYear, Year([DateOrdered])
    HAVING (((Sum(tblMealOrder.Quantity))>0) AND ((tblMenuItems.Category)<>"Drinks") AND ((tblMenuItems.MenuYear)>="YearOrdered") AND ((Year([DateOrdered]))=[Enter the year for which you would like the statistics to be listed]));

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    SELECT Sum(tblMealOrder.Quantity) AS SumOfQuantity, tblMealOrder.MenuItemID, tblMenuItems.Name, tblMenuItems.Category, tblMenuItems.MenuYear
    FROM tblOrders INNER JOIN (tblMenuItems INNER JOIN tblMealOrder ON tblMenuItems.MenuItemID = tblMealOrder.MenuItemID) ON tblOrders.OrderID = tblMealOrder.OrderID
    GROUP BY tblMealOrder.MenuItemID, tblMenuItems.Name, tblMenuItems.Category, tblMenuItems.MenuYear, Year([DateOrdered])
    HAVING (((Sum(tblMealOrder.Quantity))>0) AND ((tblMenuItems.Category)<>"Drinks") AND
    Year(DateOrdered) = [Enter the year ...] AND Year(DateAdded) >= Year(YearOrdered);
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    May 2004
    Posts
    80

    Still not working...

    This is what happens when I edit the SQL accordingly:


    Arrrghhhh! I think Access is playing silly buggers on me!!

    - Grace
    Attached Thumbnails Attached Thumbnails sql.gif  

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Hint: you have 9 opening paranthesis and only 8 closing paranthesis.

    Try adding one to the end.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    May 2004
    Posts
    80

    "...." is not a valid name, make sure it does not used invalid characters....

    Hello again

    Thanks for your post...trust me not to notice things like that!!

    The only problem I have now is that, while the fields that were set up are all fine, I can't run the query because a box appears and tells me that:

    <Name> is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

    In my experience, it's been because of syntax contained within the query, but then, that could just be a fluke. Is there any easy solution - because the query looks as though it should work!!

    Thanks

    Grace

  12. #12
    Join Date
    May 2004
    Posts
    80

    Unhappy Auuughhhh

    Hi Teddy/everybody,

    This is my code:

    SELECT Sum(tblMealOrder.Quantity) AS SumOfQuantity, tblMealOrder.MenuItemID, tblMenuItems.Name, tblMenuItems.Category, Year([DateAdded]) AS YearAdded, Year([DateOrdered]) AS YearOrdered
    FROM tblOrders INNER JOIN (tblMenuItems INNER JOIN tblMealOrder ON tblMenuItems.MenuItemID = tblMealOrder.MenuItemID) ON tblOrders.OrderID = tblMealOrder.OrderID
    GROUP BY tblMealOrder.MenuItemID, tblMenuItems.Name, tblMenuItems.Category, Year([DateOrdered]) HAVING (((Year([DateAdded]))>=Year([YearOrdered])) AND ((Year([DateOrdered]))=[Enter the year]));


    And I get a variety of error messages when I try to run the query:

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

    For anybody new to this problem, my basic intention is this:

    - To run the Year Ordered: Year([DateOrdered]) part of the query as parameter. So that the user enters say 2004, and all the records that were created (i.e. all the menu items that were ordered) during 2004 appear. YearAdded: Year([DateAdded]) needs to ensure that only menu items added to the menu in the same year - in this case, 2004 - that is entered in the Year([DateOrdered]) field, are returned.

    I hate Access right now and I don't understand it all enough to troubleshoot it myself. Hope you can help.

  13. #13
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi Grace,

    Could you by chance Zip and Post a sample of your database to look at to try to troubleshoot for you? You can make a copy of the database, remove all the pertinent data you don't wish to expose and input some dummy data. I would be glad to look at it when I get home tonight. If you can do that, then explain once more exactly what you wish to accomplish from A-Z....to make sure I got it all just right.

    hope you're having a great day,
    Bud

  14. #14
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Dear Grace

    I hate Access right now and I don't understand it all enough to troubleshoot it myself.

    I often find that one of the best ways to avoid the above is to alter the data so as to make life easy.

    For example, you could create another field in the query like this

    NewField:IIf([YearAdded]>=[YearOrdered],1,0)

    Now the criteria for you new field is just 1

    Mike

  15. #15
    Join Date
    May 2004
    Posts
    80

    Thanks Mike

    To Mike: thanks heaps, I'll have a go at that one.

    To Bud: can you post your email address? i sent you one through Dbforums but I couldn't add attachments.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •