Here's a generic function to do it. You can extend it pretty easily if desired.
'====================================================
' LICENSE/COPYRIGHT - DO NOT REMOVE THESE COMMENTS!
'====================================================
' *
' * AddDate Module
' * Copyright (C) 2007 John D. Mazza III.
' *
' * Written by John Mazza
' *
' * This library is free software; you can redistribute it and/or
' * modify it under the terms of the GNU Lesser General Public
' * License Version 2.1 as published by the Free Software Foundation.
' *
' * This library is distributed in the hope that it will be useful,
' * but WITHOUT ANY WARRANTY; without even the implied warranty of
' * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
' * Lesser General Public License for more details.
' *
' * You should have received a copy of the GNU Lesser General Public
' * License along with this library; if not, write to the Free Software
' * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
' ****************************************************************************
'
'
' PURPOSE
' Contains generic function to add years to a date in an
' arbitrary table/field combination
'
' LANGUAGE
' Visual Basic 6.0 or VBA6
'
' INSTALLATION
' Copy this module to a new module in your MS Access project
' NOTE - make sure to add a reference in Tools/References to
' Microsoft DAO 3.5 or 3.6
'
' AddYears() SUB
' Use to add a number of years to dates in a specified table
'
' PARAM: Years - Number of years to add
' TableName - Name of the table
' OrgFld - Name of field holding original date value
' DstFld - Name of field holding destination date (may be same as OrgFld)
' IDFld - Name of field used as a key column. Must be INT type and unique
'
' WARNING: Does not verify that any of the parameters are valid for your
' database. MAKE SURE THEY ARE CORRECT BEFORE CALLING THIS FUNCTION
' OR DAMAGE TO YOUR DATA MAY OCCUR!
Public Sub AddYears(ByRef Years As Variant, ByRef TableName As Variant, _
ByRef OrgFld As Variant, ByRef DstFld As Variant, _
ByRef IDFld As Variant)
Dim db1 As Database
Dim rs1 As Recordset
Set db1 = CurrentDb
' Fail silently on any errors
On Error Resume Next
CheckParmsOK = False
If (IsNull(Years) Or IsEmpty(Years)) Then
CheckParmsOK = False
Else
CheckParmsOK = True
YrsAdd = Val(Years)
End If
If (IsNull(TableName) Or IsEmpty(TableName) Or Trim(TableName) = "") Then
CheckParmsOK = False
End If
If (IsNull(OrgFld) Or IsEmpty(OrgFld) Or Trim(OrgFld) = "") Then
CheckParmsOK = False
End If
If (IsNull(DstFld) Or IsEmpty(DstFld) Or Trim(DstFld) = "") Then
CheckParmsOK = False
End If
If (IsNull(IDFld) Or IsEmpty(IDFld) Or Trim(IDFld) = "") Then
CheckParmsOK = False
End If
If CheckParmsOK Then
Qry = "SELECT " & IDFld & "," & OrgFld & " from " & TableName & ";"
Debug.Print ("AddYears: " & Qry)
Set rs1 = db1.OpenRecordset(Qry, dbOpenSnapshot)
While Not rs1.EOF
OrgDate = rs1(OrgFld)
RecNo = rs1(IDFld)
If Not IsNull(OrdDate) Then
DtDue = DateAdd("yyyy", YrsAdd, OrgDate)
UpQry = "UPDATE " & TableName & " SET " & DstFld & " = #" & DtDue & "# " & _
"WHERE " & IDFld & " = " & Str(RecNo) & ";"
Debug.Print ("AddYears: " & UpQry)
db1.Execute (UpQry)
End If
rs1.MoveNext
Wend
rs1.Close
End If
End Sub
