In Access 2003 Calculate a future date from a date column

[Guest]

I am building a Access 2003 database where I am using the DateSerial
calculator. I am able to get the future date to calculate using two fields in
a form but I am not able to figure out how to calculate a future date from a
column of dates. As an extra twist the column of dates is in a subform of
the mainform with the field containing the future calculated date.

Example: I have a form that displays a Procedure on Equipment with a field
showing next Review Due Date and in that form is a subform with the Review
Date history with a column of dates the procedure was reviewed, a comment
and name field is also included in the field.

I also like the smart tag idea and when I used it on the Next Due Date Field
I succeeded in having Outlook open an email or schedule meeting window. It
was limited to inserting the Review Due Date from the field with the smart
tag but I was hoping it would also insert information about the procedure
needing review into Outlook.

 

[Mike Labosh]

I am building a Access 2003 database where I am using the DateSerial

calculator. I am able to get the future date to calculate using two fields
in
a form but I am not able to figure out how to calculate a future date from
a
column of dates. As an extra twist the column of dates is in a subform of
the mainform with the field containing the future calculated date.

Example: I have a form that displays a Procedure on Equipment with a
field
showing next Review Due Date and in that form is a subform with the Review
Date history with a column of dates the procedure was reviewed, a comment
and name field is also included in the field.

I also like the smart tag idea and when I used it on the Next Due Date
Field
I succeeded in having Outlook open an email or schedule meeting window.
It
was limited to inserting the Review Due Date from the field with the smart
tag but I was hoping it would also insert information about the procedure
needing review into Outlook.

Have a look at the DateAdd() VBA Function. You pass it the start date, an
interval (day, week, month, etc) and how many of those intervals to add to
the start date. The return value is the destination date as a Date/Time
data type.

 

[John Vinson]

I am building a Access 2003 database where I am using the DateSerial
calculator. I am able to get the future date to calculate using two fields in
a form but I am not able to figure out how to calculate a future date from a
column of dates. As an extra twist the column of dates is in a subform of
the mainform with the field containing the future calculated date.

Example: I have a form that displays a Procedure on Equipment with a field
showing next Review Due Date and in that form is a subform with the Review
Date history with a column of dates the procedure was reviewed, a comment
and name field is also included in the field.

I also like the smart tag idea and when I used it on the Next Due Date Field
I succeeded in having Outlook open an email or schedule meeting window. It
was limited to inserting the Review Due Date from the field with the smart
tag but I was hoping it would also insert information about the procedure
needing review into Outlook.

You can use the DateAdd function in a Query to calculate a future
date. It's not clear from your post how this date should be calculated
and from what source though!

Say you have a ReviewedDate field, and the Next Due Date is six months
later. In the Query you can put a calculated field

Next Due Date: DateAdd("m", 6, [ReviewedDate])

Hope this gives you a step in the right direction!


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

John,

Thanks! The source is the field called [ReviewedDate] in a subform called
"Review History". The Review Date source is a column of dates and I want to
calculate the Next Due on the most recent date from the column.

The subform "Review History" is inside my form named "Equipment Procedure
Review" and I want the Next Due field to show up in this form. ( -From it I
will create a report/query of Next Due reviews of records between a range of
dates specified by the user.)

Perhaps I can use a filter on the Review Date column to get the most recent
date to appear in the field Next Due where the calculator/Date Serial is. My
other idea was to sort the column of Review Dates so that the most recent
date appears at the top of the column then have it appear in the Next Due
calculator field. I wish I had enough know how to make it so!

I am building a Access 2003 database where I am using the DateSerial
calculator. I am able to get the future date to calculate using two fields in
a form but I am not able to figure out how to calculate a future date from a
column of dates. As an extra twist the column of dates is in a subform of
the mainform with the field containing the future calculated date.

Example: I have a form that displays a Procedure on Equipment with a field
showing next Review Due Date and in that form is a subform with the Review
Date history with a column of dates the procedure was reviewed, a comment
and name field is also included in the field.

I also like the smart tag idea and when I used it on the Next Due Date Field
I succeeded in having Outlook open an email or schedule meeting window. It
was limited to inserting the Review Due Date from the field with the smart
tag but I was hoping it would also insert information about the procedure
needing review into Outlook.

You can use the DateAdd function in a Query to calculate a future
date. It's not clear from your post how this date should be calculated
and from what source though!

Say you have a ReviewedDate field, and the Next Due Date is six months
later. In the Query you can put a calculated field

Next Due Date: DateAdd("m", 6, [ReviewedDate])

Hope this gives you a step in the right direction!


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

John,

Thanks! The source is the field called [ReviewedDate] in a subform called
"Review History". The Review Date source is a column of dates and I want to
calculate the Next Due on the most recent date from the column.

Nitpick: The source of the field should not be considered to be a
Subform. Data is not stored in forms, but in Tables. Forms are just
windows for the data.

The subform "Review History" is inside my form named "Equipment Procedure
Review" and I want the Next Due field to show up in this form. ( -From it I
will create a report/query of Next Due reviews of records between a range of
dates specified by the user.)

Again... if you want to create a Report, then it's not necessary to
put the value on a Form. You can instead put it in a Query based on a
Table.

Perhaps I can use a filter on the Review Date column to get the most recent
date to appear in the field Next Due where the calculator/Date Serial is. My
other idea was to sort the column of Review Dates so that the most recent
date appears at the top of the column then have it appear in the Next Due
calculator field. I wish I had enough know how to make it so!

If you want to *display* data from the Reviews table on the Report,
and also display the NextReview, you can set the Control Source of a
textbox on the Report (or the Form, for onscreen display) to an
expression like

DateAdd("m", 6, DMax("[ReviewDate]", "[Reviews]", "[EquipmentID] = " &
[EquipmentID]))

DMax() will find the largest (most recent) ReviewDate for the
Equipment item currently being displayed on the Report (or form);
DateAdd will add 6 months to that date.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps