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