IIF statement

[mana2]

Hi,

I'm trying to calculate the value of a field based on the other columns in the table. It's working in a form but I need to save the value in the table. I'm selecting the default value of the field and entering the following:

IIf(DateDiff('m',[Vesting Start Date],Date())>12,0.25*[Number of Options]+(DateDiff('m',[Vesting Start Date],Date())-12)*0.03*([Number of Options]-(0.25*[Number of Options])),0)

It doesn't recognize any of the column names like Vesting Start Date. Is it possible to calculate the value of a field based upon the other columns?

Thanks

 

[Pat Hartman]

You can't reference other fields and you can only use a limited set of functions in the table definition. Aside from that, storing calculated values violates second normal form and should be avoided at all costs. Just calculate the value in your queries.

 

[mana2]

Thanks

Thanks so much