Date calculations

[Guest]

I am creating a database that tracks tasks, their completion dates and due
dates.

The due date is based on the completion date and the frequency (e.g.
quarterly). I used this in Excel and it was able to calculate the due dates.


I tried to created a calculated field in my query (which has the comp date
and freq), but I'm getting errors.

How do I get this to calculate?
Thanks

 

[Rick B]

How did you do it in Excel?

You don't tell us the formula to use. Is due date before or after the
completion date? Personally, I try to complete items just before they are
due.

What does frequency have to do with it? A frequency is used to create a NEW
instance of a task at some predefined interval. It has little to do with
the current task.

If I have a particular task that is due march 1st, then my completion date
(hopefully) will be some date prior to March 1. My next task (totally
separate record) would take the DUE DATE and add 30 to it (monthly) or 90
(quartertly) or 365 (yearly).

I'm not sure I follwo your structure or what you are calling a "due date" a
"frequency" and a "completion date".

 

[Guest]

Our due dates are based off of the completed date. The due date is basically
a recurrance. If we completed the task today and it's an annual task
(frequency), the due date becomes today's date plus 1 year (or 365 days).

In Excel, the dates were converted it's numeric form, the days were added
and converted back to a date format.

What we're looking to do is run a query of what is due in a time frame (e.g.
within the next week).
Carol

 

[Rick B]

I'm guessing then that the "due date" is not a field in a table. You would
like to calcualte it?

If so, add a new column to your query like....

DueDate: CompletedDate + 365

Then put criteria under that that says


Between Date() and Date()+7

 

[John Vinson]

Our due dates are based off of the completed date. The due date is basically
a recurrance. If we completed the task today and it's an annual task
(frequency), the due date becomes today's date plus 1 year (or 365 days).

In Excel, the dates were converted it's numeric form, the days were added
and converted back to a date format.

What we're looking to do is run a query of what is due in a time frame (e.g.
within the next week).

If the Frequency is stored as an integer number of days, you can
calculate the due date "on the fly" in a Query:

DueDate: DateAdd("d", [Frequency], [CompletedDate])

If, on the other hand, Frequency contains text such as "Annual",
"Monthly", "Weekly" you could use

DueDate: DateAdd(Switch([Freqency] = "Annual", "yyyy", [Frequency] =
"Monthly", "m", [Frequency] = "Weekly", "ww"), 1, [CompletedDate])

In either case DueDate becomes a date/time field which can be used for
sorting or searching; e.g. you could put a criterion on the Criteria
line under the calculated field of

BETWEEN Date() AND Date() + 7

John W. Vinson[MVP]