Software

Add upcoming renewal dates to your Access database

Calculating future dates in Access is easy, thanks to the DateAdd function and SQL UPDATE command. Mary Ann Richardson walks through an example to demonstrate this technique.

When you need to calculate a future date based on a date already entered in a date field, you can use the DateAdd function. Combine DateAdd with the SQL UPDATE command, and you can add a future date to hundreds of records with a single line of code. For example, say you keep track of the dates when customers subscribe to your five-year warranty service in the table shown in Figure A.

Figure A

access table

The blank column RenewalDate has been added as a Date/Time field. You would like to add a value to the RenewalDate field of each record that indicates when each customer's warranty comes up for renewal. Follow these steps:

  1. Open the Query Design Grid.
  2. Add the Subscriptions and Renewals table.
  3. Go to View | SQL View, which opens the SQL Window.
  4. Enter the following code at the prompt, as shown in Figure B:
UPDATE [Subscriptions and Renewals] SET [Renewal Date] = DATEADD("yyyy",5,[Subscription Date]);

Figure B

function

  1. Run the query.
After the query is run, the RenewalDate field will be filled with dates five years from the date entered in the Subscription Date field for each record (Figure C).

Figure C

future date


Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox