Calculating next due date in a form

[Martin Hopkins]

Hello,

I have certain jobs that need doing every so often and not sure how I can
warn users when the job is due.

e.g

In the Job table I have:

Job_id, Job Description

1 Receiver Check

2 Bearing change

For each installation there is an install table which links to job_id

job_id, Date installed

1 12-May-2002

I know that the receiver should be checked every 5 years. How do I set a
fieldin a form or the table that shows the due retest date i.e 5 years from
12 May 02?

Due I add a field to Job table called Due Exam, as below, if so what would
be the best format for the filed , DATE/Time or Number

Job_id, Job Description Due Exam

1 Receiver Check 5 years

2 Bearing change 3 years

But not sure how to reflect that in the form. !!! Any help greatly
appreciated.

Martin Hopkins

 

[Tom Lake]

Hello,

I have certain jobs that need doing every so often and not sure how I can
warn users when the job is due.

I know that the receiver should be checked every 5 years. How do I set a
fieldin a form or the table that shows the due retest date i.e 5 years
from 12 May 02?

Due I add a field to Job table called Due Exam, as below, if so what would
be the best format for the filed , DATE/Time or Number

Make a calculated Date field:

DueExam: DateAdd("yyyy", 5, [Date installed])

Tom Lake

 

[Klatuu]

Where you carry the period between maintenance should definitely be a numeric
field. Date/Time data types are a point in time, not a duration. Duration
is a calculation between two times and is not represented as point it time.
The result of the calculaton may be a date/time. The only difficulty here is
knowing what the duration value represents. Year, Month, Day, Hour, etc? If
your calculations will always be in years and you expect that the business
rules will never change, then it may not be an issue; however, I would
recommend two fields. One Numeric Long to carry the value and one text to
carry the interval. I would also suggest the value you carry in the interval
field be what is used in the date functions to simplify the calculation. For
example "d" for 1 day and "yyyy" for one year. So now, your calculation
becomes:

NextMaintDate = Dateadd([MaintIntervalCode], [MaintIntervalVal],
[LastMaintDate])