iff 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

 

[dhookom]

Trying to set the default value with values from other fields in the same record won't work. It is basically too late.

If you really need to store a value that can be calculated, you can use code in the before update event of the form to set the field value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

mana2

I have been following your posts with interest, and Duane has done a great job in supporting you. (You may want to reward him with a star.)

This post raises three points of interest...
- In general, as part of "normalization", calculated results are avoided. One of the reasons is that calculated results become a "maintenance item", and is subject to errors or inaccuracies.

Review for further clarification...
Fundamentals of Relational Database Design
Download document
Read on-line (HTML)

My "rule of thumb" when I choose to break this rule is if the calculation is static, and when storing the calculation makes "sense" in that it saves work on other queries. For example, storing monthly balances in a G/L balance file.

- When storing a complicated result, I feel it often best to use Event Procedures instead of using formulas in unbound text boxes. Unbound text boxes work, and a lot of people use them, but in the code, they will "Requery" the text box to update the value. In your case, you are trying to use an IIF statement (more often used for unbound controls), and update the bound control source. Do-able, but I prefer to use code because you still have to use code to update the control.

- Centralized updates. Access is very flexible, and allows you many approaches to accomplish a task. However, this flexibility may sometimes lead to more work and errors in calculations. The reason is that later on a calculation may need to be "tweaked". The "tweak" may be done at one point, perhaps on one form, but not at another point. For example, results on a form and a report may differ because different formulas were used.

My preferred approach is to use a centralized function to perform the calculation or update. This way, regardless of where the calcuation is called from (query, form or report), it is performed at the central point -- consistant.

...Moving on
Let's look at your IIF statement...

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)

Basically, if the vested date is over 12 months, use a formula to calculate the result. Otherwise, use 0.

Removing the DateDiff calculation, I think I get...

0.25 * NoOfOptions 
+ (VestedMonths - 12) 
* 0.03 
* (NoOfOptions - (0.25 * NoOfOptions))

For example, with 100 Options and a start date, I get an answer of 58.75.

(Sidebar: See FAQ: Avoid space characters in any 'Name' - Why ? )

After having read the Fundamentals of Relational Database Design by Paul Litwin, you may decide not to store your calculated value since the number of months vested will be changing. If calculation has a dollar value tied to it, then by all means, store the dollar value as a transaction, but using the above appracoh, the only way you update the stored value is by opening the form, retrieving the record, and forcing an update. Hmmm.

This leads me to point two and three.

Here is one of various "functions" you can use to perform the above IIF statement.

Function UpdateVestedFormula(strEmployee as String) as Single

Dim rst as DAO.Recordset
Dim strSQL as String, strQ as String
Dim intNoStockOptions as Integer, intVestedMonths as Integer
Dim sngVestedCacluation as Single

strQ = Chr$(34)
strSQL = "SELECT * FROM StockOptions" _
& " WHERE Employee = " & strQ & strEmployee & strQ

Set rst = CurrentDB.OpenRecordset(strSQL)

With rst
   If .RecordCount Then
      .MoveFirst
      intVestedMonths = DateDiff("m", ![vesting start date], Date) - 12
      intNoStockOptions = ![Number of Options]

      Select Case intVestedMonths 
         Case < 1 ' zero or less than 0
            sngVestedCacluation = 0

         Case Else
            sngVestedCacluation = (0.25 * intNoStockOptions) _
            + intVestedMonths * 0.03 _
            * (intNoStockOptions - (0.25 * intNoStockOptions))

            'If you really want to store / update the calculation
            .Edit
            !VestedCalc = sngVestedCaculation
            .Update

      End Select
   Else
      sngVestedCacluation = 0
   End If
   .Close
End With

Set rst = Nothing

UpdateVestedFormula = sngVestedCacluation 

End Function

The function is stored in the "module area". From the menu, "Insert" -> "Module". The top of the module should include...

Option Compare Database
Option Explicit

This function can be called anywhere -- a form, a report or a query. Note that by removing the "update" part, you can use the function to return the current vested calculation, and not update the table. (Hint)

The return value can be tweaked for your needs.

I defined VestedCalc as Numeric, decimal, percision 18, scale 2, decimal 2.

Results...
[tt]
Employee Vesting Start Date Number of Options VestedCalc
Asimov 01-Jan-03 100 58.75
Brooks 15-Jun-95 1000 2635
Jordan 30-Mar-90 1500 6078.75
LeGuin 01-Jan-05 500
[/tt]

Richard

 

[lespaul]

Like Richard said, sometimes it is appropriate to store a calculated field, but it's rare. The only calculated fields I store are ones that may be modified. Let me explain, our jury program calculates the amount of pay a juror should be paid. I store these calculations in a separate table because occasionally these numbers have to be changed. Public Employees don't get paid during their regular working hours, so we have to enter their regular start and end time so we can determine if the time they served was their own or work time. Unfortunately, some employees (like firemen) work 36 hours on 36 hours off and it's really hard to calculate how much they should get paid with rotating schedules. So, the program determines how much it thinks the person should get paid, stores that information, and then the accounting department has the ability to modify that information. I can still calculate the original information from the original data, but the calculations may be different.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual