Autocalculate Dates

[Craig Kelsey]

I want to set up a database with employees names, start
date and end date.

What I want to do is enter a start date and end date in
the table and I want a result to autoenter into the Weeks
column.

Eg.
Start Date = 12/5/2002
End Date = 17/5/2002

Result auto enter would be 2 weeks.
How could I get this into the table as a auto entered item.

I can do it on a report page by =StartDate - EndDate..

But I cant figure out the calculation for autoenter into
the table.

Please Help

 

[Jeff Boyce]

Craig

Another thought ...

If you already have the start and end dates, why would you need to store
their "difference"?

Any place (query, form, report) you'd need the difference, in weeks, you
could use an expression, with the DateDiff() function (it will give you a
difference in "weeks" if you ask nicely).

You might ask "why all the extra work?". What happens to your calculated
value if someone decides that one or both of the dates entered were
mis-entered and "fixes" the date(s)?

The data bigots (myself included) and purists will also point out that
relational design eschews storing calculated fields.

Finally, storing redundant information (including calculable values) takes
up extra (but admittedly cheap) hard drive space.

By the way, what you're looking for would be called a trigger in SQL-Server
or other heavier-duty databases -- but does not exist in Access.

If you still want to do this, you'd have to create a form via which start
and end dates would be entered (it's a very good idea to work through forms
anyway, rather than directly in the tables). You'd have to add code behind
the form, to respond to the AfterUpdate events of both the start and end
date controls, to calculate and place the calculation in a control
displaying weeks.

NOTE! If your "weeks" control on the form is not bound to any field, you'll
still get the calculated weeks displayed, as I suggested above.

Good luck

Jeff Boyce
<Access MVP>