Unanswered: The current year criterion in a query continued (and other query criteria questions!)
Teddy told me that to return a field with criteria set to the current year, you need to pass a date function, using Year(date)
However, I'm not sure where to put this within my query; and then, exactly what text to place. Can anybody offer any ideas based on my screen dump below?
Note that this would need to be placed under a new (but already existing) field - DateAdded - so that users would be able to easily see the name of the year from which the menu item was derived. For example, if I query all the orders placed between a date range of, say, 3/3/01 and 20/5/04, and 30 items return; I'm going to want to very quickly and easily know which year that menu item was added to (not ordered from) the menu (this comes under DateAdded, which is found in the MenuItems table). In this way, if Roast Chicken was added to the menu on the 26/7/02, then this new DateAdded calculated field would simply show up with 2002, to tell me that that was the year of that item's menu.
I also need a relatively simple way of validating that an order for an item is not placed on a date that is before the date that that item was added. i.e. Roast Chicken was added to the menu on 17/8/03, and so I cannot place an order for this item before the 17/8/03.
Finally, how to run a calculated field within a query to determine whether or not a menu item is current according to a) the year it was added to the menu and b) the current year? e.g I added Nut Sundae to the menu on 28/12/03, and it is now 5/4/04, and therefore I would like a calculated field -
- to tell me that it is not current, because the year it was added and the current year do not match, by displaying No in its field.