CODEWITHSUNDEEP
datems-accesscalculated-field
cdbean
cdbean

Reputation: 21

MS Access use DATE() in a calculated field

I am using Microsoft Access 2016. I am trying to find out how many years exist from the current year until a future year. I have a column that is end_date. I am trying to create a calculated field that is essentially YEAR(end_date) - YEAR(current_year). I tried to use YEAR(DATE()) but DATE() is not allowed to be used in a calculated field apparently.

Is there no way to do a calculation like this?

Upvotes: 1

Views: 2924

Answers (3)

Gustav
Gustav

Reputation: 55841

In a form, you can use this expression as controlsource for a textbox:

=DateDiff("yyyy",Date(),[EndDate])

However, that return the difference in calendar years. To find the count of full years, use a function like AgeSimple and this expression:

=AgeSimple([EndDate])

Upvotes: 0

cdbean
cdbean

Reputation: 21

I changed my thinking to calculate this in a form. It does not seem good practice to have a field in a DB that changes everyday.

Upvotes: 0

Erik A
Erik A

Reputation: 32642

Nope. Calculated fields are cached and static, so are NEVER allowed to contain ANY information that will change over time, due to system settings, or anything else that is not directly entered in that row.

However, you should not be using calculated fields anyway. See http://allenbrowne.com/casu-14.html, among many posts advocating for not using calculated fields.

Instead, use queries to do calculations. That way, you won't have any trouble using the current date, and won't have to deal with the possible errors and portability issues calculated fields come with.

Upvotes: 2

Related Questions