Calculated Field: Today() - [DOS]

[mrmmickle1]

Can someone please assist me in the creation of a calculated field.

The Calculated field needs to be: Today's Date - [DOS]

I can't get these to work:

=GetDate - [DOS]


=Today() - [DOS]
=Date() - [DOS]

I get errors on all of the above attempts. Any help would be much appreciated. I am simply trying to calculate the days that have gone by since the [DOS]

[June7]

If you are doing this in query, remove the = sign.

[mrmmickle1]

June7,

Thanks for the reply.

I'm trying to do it in an actual table field. Is this considered bad practice?

I also tried this formula with no luck:

=Format(Date(),"mm/dd/yyyy")-[DOS]

If it matters..... [DOS] is in Short Date Format

[June7]

Some like, some don't. If you need to build db for backwards compatibility, is bad.

However, not all functions are available to calculated field in table. If they don't show up in the expression builder, then not available. Date() is not an available function.

You will have to do this calc in query or textbox.

Today() is not even an intrinsic function, I don't know what GetDate is.

Format() would result in a text value, not an actual date and that expression should fail even if used in query or textbox.

[rpeare]

if you're storing the DOS (date of service) field why do you need to store the calculated field at all? You can just create it with any query/report you run on the data.

[June7]

rpeare, it is my understanding that Calculated field (introduced with Access 2010) expression dynamically recalculates when the raw data changes and does not store calculated result. However, I could be mistaken - according to Allen Browne http://allenbrowne.com/casu-14.html

It is intended for simple calcs like Price * Quantity and not all functions are available.

[mrmmickle1]

I ended up putting in a Form TextBox as you suggested. =DATE()-[DOS] was the proper syntax.

Thanks so much for the help.

[rpeare]

june, I believe the values *are* stored because the section dealing with:

This may seem simple, but it creates more problems that it solves.....

from that same article for various reasons

states that if you change the calculation, all your existing data will not be converted at all which screams to me it's being stored. This is in conjunction with other problems also listed in that same article.

Plus... I'm a minimalist and don't believe in storing anything I don't have to!

[June7]

I've never used them but just did a simple test. Created Calculated field and saved table then modified the expression. Results re-calculated. So don't really know what Allen is warning about. Maybe this is one time Allen misses the mark.

[mrmmickle1]

Thank you both for the valuable input. I appreciate it! This is good to know.