write a custome "if" function in access

[hamid sajjadi]

hello every body.
I've written a code in my excel.Now I want to write such formule in access too.how can i write this "if" statement in access?

=IF(AND(F11=8484,OR(G11=20,G11=21)),8470,IF(AND(F11=8484,OR(G11=40,G11=41)),8472,F11))

each cell in the above formule reffers to a field in my table (query).for example F11 reffers to "account code" and G11 reffers to sub account code.i want to put this code in a new calculated field in access.
thank u

 

[Marbles]

This should work.

Expr1: IIf([A]=8484 And (=20 Or =21),8470,IIf([A]=8484 And (=40 Or =41),8472,[A]))

The letters are the Field Names that you change to yours.

Best done in a Query instead of the Table.

 

[hamid sajjadi]

is it possible to use many "or" expression?indeed I need to have over 10 "or" expression.
another question.why y say "Best done in a Query instead of the Table.".what is the advantage?

 

[Joe4]

is it possible to use many "or" expression?indeed I need to have over 10 "or" expression.

Yes, you can do multiple AND or OR statements.

Best done in a Query instead of the Table.".what is the advantage?

While it is true that newer versions allow you to create and store some calculations right on table level now, it is usually best to keep doing them in a query instead, for a few reasons, including:
1. The table-level calculations are limited, in regards to what you can do.
2. No other database program allows this. So if your database ever needed to grow and you needed to migrate it to something bigger like SQL, it wouldn't work (not compatible if you do that).
3. It violates the rules of data normalization (which all well-designed databases should strive for) in which none of your table fields should be dependent on other table fields.