Calculating a future or past date in Access

Applies to
Microsoft Office Access 2003
Microsoft Access 97, 2000 and 2002

In Access, you can calculate a future or past date from a starting date by using an expression. For example, you can enter a date in a control on a form and calculate a date that is six months in the future from the original date. This article demonstrates this technique by using the DateSerial function to calculate the future date in the controls on a form. The calculated date can then be used in other calculations.

To calculate a future date by using the DateSerial function

  1. In the Database window, click Forms under Objects, and then click New.
  2. In the New Form dialog box, click Design View, and then click OK.
  3. Using the Text Box tool in the toolbox, add two unbound text boxes to your form.
  4. Set the Name property for one text box to StartDate and the other to FutureDate.
  5. Set the Format property for the StartDate text box to Short Date.
  6. Set the Control Source property for the FutureDate text box to the following expression:
=DateSerial(year([StartDate]), month([StartDate])+6, day([StartDate]))
  1. Switch to Form view.
  2. In the StartDate text box, type a date (mm/dd/yy), and then press the TAB key.
    The value displayed in the FutureDate text box is six months after the date in the StartDate text box.

Inserting a plus (+) followed by a number after the year, month, or day in the appropriate part of the function calculates a date that number of units in the future. Inserting a minus (-) followed by a number in the appropriate part of the function calculates a date that number of units in the past.

 
 
Applies to:
Access 2003