Get Date() to update upon database opening

[nick1408]

Hi Guys,

I am running the following in a calculated field:

IIf((([Today]-[Start Date])/(([Due Date])-[Start Date]))>1,"Failing",IIf([Amount Complete]<(([Today]-[Start Date])/(([Due Date]-28)-[Start Date])),"At Risk","On Track"))

The actual formula works fine but the issue I have is that [Today] doesn't auto-update to today's date. Currently it is set to a field that defaults it's value to =Date() but as I am sure all of you Access wizards know this doesn't actually update each time. The [Today] field is purely used for this formula so can be as ugly or as neat as needs to be. I have stuffed around with the macros thing but I feel this would be easier to do with VBA, I just don't know how to add VBA code to a table.

Thanks for the help

 

[Cronk]

In your code, replace [Today] with Date()

Incidentally, you would not need to have all the square brackets had you not included spaces in your field names.

 

[nick1408]

In your code, replace [Today] with Date()

Incidentally, you would not need to have all the square brackets had you not included spaces in your field names.

If I change all the [Today] to Date() I get told the expression cannot be used in a calculated column. This doesn't make sense to me

There are spaces as I pinched the original template from the Microsoft website. I do understand it is bad form