Expression Builder - Number Countdown

Question

I have a database that manages training and I'm trying to create a countdown column. For example, if I have an expiration date of 3/2/18 and based on today's date, I want a column that will show that I have 1 day left of training until it expires (or if I have an expiration date of 4/1 and today is 3/1 then I need it to say 30 in that column and update based on the current date). I have been trying to use the expression builder but I can't seem to get the formula right.

Any help would be appreciated! Access 2016 - Windows 7

Thank you

Answer 1

I would not create a calculated field. It's fairly simple to include the expression in a query or control source on a form or report. There is generally no good reason to store the value in calculated field.

There are some functions or expressions that are not allowed in calculated fields. Apparently Date() is one of them.

Answer 2

I was able to put the expression in the calculated field but when I try to save my table I get the following error:

"The expression [Expiration Date]-Date() cannot be used in a calculated column."

Am I going about this the wrong way?

Thank you,

Andrew

Answer 3

Correct. The Date() function always returns the computer system date.

Answer 4

Hi Duane,

Thanks for the reply, would this be dynamic based on the current date? For example if its 5 days left today, tomorrow it would update to 4 days left?

Thank you,

Andrew

Answer 5

Hi Andrew,
You should be able to do simple math with the date
[Expiration Date] - Date()

This can be a column in a query or control source of a text box.