Complex Formula... Need some help

[IanHazelwood]

Hello all



I'm trying to put in a calculated field into my database.

I'm doing a mock restaurant scenario, and I'm pretending that if each customer were to order an appetizer, entree, and dessert together, they get to use a coupon discount.

I'm trying to make the CouponDiscount field a calculated field so that if the appetizer, entree, and dessert are all filled out, then it will apply a "yes" if so, and "no" if not.


Ive inserted an image of what my formula looks like, but when I try to apply it, I get the invalid syntax, operand without an operator error message.

Any help would be much appreciated

[CJ_London]

two things.

1. Your data is not normalised - may not be a problem now, but will be in the future
2. this sort of calculation should not be done in a table - but in a query or form

with regards your formula,
1. all you need to know is they have ordered an appetizer, entree, and dessert together. It doesn't matter whether it is Nachos etc.
2. You want a checkbox - which is Boolean, so the values are true, false, or -1, 0 or yes/no - not "Yes"/"No"

So all your formula needs to be is

isnull([Appetiser])+isnull([Entrée])+isnull([Dessert])=0

and format the field as yes/no

[IanHazelwood]

So I changed my "No" values to null values instead and it made the formula much easier. I think I've got it figured it out from here.

[TommyK]

Just a suggestion, it appears that this table is an all encompassing spreadsheet. If it were me I would have a table for the ticket number, with the date/time the ticket was generated, possibly the customer name, payment amount, maybe payment method, credit card company confirmation number, etc. Then a table for your menu items with a one column for item type (appetizer, entree, Dessert, drink, alcoholic drink, and maybe a complimentary item, don't forget the gratuity line.) The dollar value of each item should be in it's own field, then, of course a quantity column. This makes totaling up the ticket easier. It also makes updating prices easier.... one safe source. Then a query would link these two tables together using the order number. The query would also do the price extensions, qty x price. The total for the ticket would be calculated as a form / report.

At a very minimum the price should be in a number field by itself.