How to calculate a timeframe when the end date may be a specific date or a current date

[MCregan]

I am making an employee database for my agency and have hit a snag. I want to present a field that calculates the number of years an employee has held a specific job title from a starting and ending date. The table I use is simple with the following fields: Employee ID, Job Title, Start Date, End Date, and Total years. Total years is calculated by (End Date - Start Date)/365. I then use the data from this table to populate a subform that is incorporated into an employee information form. It works beautifully except when there is no end date in the table because that is their current position. I am thinking I need to do something more complex but I am a pretty basic/beginner access user so I am not sure what will work best. I appreciate any suggestions.

[June7]

Consider use of Nz() function:

(Nz([End Date], Date()) - [Start Date])/365.

Advise not to use spaces nor punctuation/special characters in naming convention.

[Micron]

The most repeated suggestion you'll get: don't store calculations. Do that in forms/reports/queries. So no field anywhere for total years. That should be calculated as required, not stored. Maybe your form (or query or report) has a calculated field that uses IIF function. So subtract if there are 2 values, and show nothing if both values are not present. Your query calculated field could be (untested) TotalYears: IIF(IsNull(EndDate),Date-StartDate,StartDate-EndDate) but I would probably use DateDiff function instead so that I could get the time span in whatever value I wanted (subject to the values that DateDiff can return of course). You might want to Google DateDiff function. Note that spaces and special characters (save for underscore) or reserved words are not used by pros (or me, for that matter ). So EndDate, not End Date and no use of the word Date for my own objects.

[MCregan]

When I enter that for the expression in the field properties an error message says it cannot be used in a calculated column. The expression does work as an update query so now I just need to remember how to make the update query run when the subform opens...

[June7]

Don't save to table, just calculate in query or textbox.

[orange]

Length of time in a position sounds a lot like Age (Birthdate to current Date) in concept. There are several AGE functions in the various forums.
Seems all employees would have a StartDate, and those people who are no longer in the position would have an End/Termination Date. For current Employees with no End Date, seems you could use CurrentDate to determine their time in the Position. The NZ(EndDate, Date()), as June suggested, seems appropriate.

You shouldn't be storing the calculation in a table.

[Micron]

When I enter that for the expression in the field properties an error message says it cannot be used in a calculated column.

That seems like you're trying to create the calculated field in a table. As per posts 3 and 5 - don't store calculations in a table. Might also mention that calculated table fields have very narrow limits on what you can do. Note "can do" not "should do".

[ssanfu]

See: Calculated Fields by Allen Browne