If-Then Formula for Calculated Field in Access

[mbelc]

Hi,

I'm sorry to post again, but I am just wondering if it is possible to build an expression that is of the following sort:

Field 3 is where I want the number to show up.

If [Field 1]=x, I want Field 3 to calculate/show the result of [Field 2]*3. If [Field 1]=y, I want Field 3 to calculate/show the result of [Field 2]*5.

How do I build an expression like this? (I am only familiar with excel formulas and the one I use in excel did not work for this).

Thanks

[CJ_London]

calculated fields in tables are generally a bad idea - just because you can doesn't mean you should

but if you want to go that route, in access you use IIF rather than IF i.e. (I'm assuming field1 can only be x or y)

iif([Field 1]="x",[Field 2]*3,[Field 2]*5)

or

[Field 2]* iif([Field 1]="x",3,5)

[mbelc]

calculated fields in tables are generally a bad idea - just because you can doesn't mean you should

but if you want to go that route, in access you use IIF rather than IF i.e. (I'm assuming field1 can only be x or y)

iif([Field 1]="x",[Field 2]*3,[Field 2]*5)

or

[Field 2]* iif([Field 1]="x",3,5)

Very helpful, thanks so much!

Though I am wondering why you think calculated fields are a bad idea?

[pbaldy]

I'm sorry to post again

Don't be; it would be a boring site without people posting questions.

In addition to whatever thoughts Ajax may have:

http://allenbrowne.com/casu-14.html

[CJ_London]

No, Allen pretty much has it.