automatic calculation of date differences

[jjarman]

Hoping someone can help, again!

The project I'm working on is an action list with the following tables;

itemnumber - autonumber PK
originator - text
dateentered - date/time
actiondescription - text
assignedto - text
targetdate - date/time
forecastdate - date/time
actual date - date/time
remarks - text
due - text (not sure if this will be correct)

Basically, when a new task is entered, all info will be manually filled in except last 3 fields. targetdate needs to be locked after entry, and forecastdate initially set to targetdate.

"due" should show how many days between dateentered and forecast date, and preferably update everytime the DB is opened. IF the current date is beyond the forecast date, "due" should read "OVERDUE".
Changing forecastdate would obviously reset the "due" field to show how many days left to complete. If a date is entered into actualdate, then "due" should show COMPLETE.
Reports will be generated based on what is coming due, what is over due, and not show what is already complete.

This has already been done in Excel, but can it be done in Access? Any help is REALLY APPRECIATED!! Thanks

 

[plog]

You don't store calculated/derived data. That means 'due' shouldn't be a field in a table, instead you would calculate it whenever you need to use it. You would use the DateDiff function to do that: http://www.techonthenet.com/access/functions/date/datediff.php

Because you want 'due' to be more complex than just showing the difference in days between dates, you would need to also use an IIF statement: http://www.techonthenet.com/access/functions/advanced/iif.php

 

[jjarman]

OK, if I can get my head wrapped around this... what you're saying is I should maybe have a field in a report that does the calculation? As long as the value is not stored anywhere, and calculated on the fly it should work.
The IIF statement makes sense... I'm assuming I can use a text box for "due" as it will have either text or a number, correct?

 

[plog]

All of that is correct. A further suggestion is to look at the Excel file to see how it is doing it and then basically recode the calculation using Access functions (DateDiff and IIF).

 

[jjarman]

Got it working, thanks!!
The only benefit to doing it in Excel was the fact that I could see all the relevant information in the table (overdue items) as it would update automatically when the file was opened... in Access I have an extra step of generating a report. Regardless, I think Access is a better way to store the information.
Thanks again.