change the date for new year in table

[Guest]

I have a table that contains schedule PM records for monthly, quarterly,
annualy scheduled dates. Now that the year is ending I would like it to read
the dates for 2007 instead of 2006. I would like to keep the records of
2006. Is there any way I can do it without typing all the dates again for
2007? If possible, I would like to use the same table for 2007.

Thanks for help

 

[Larry Linson]

I have a table that contains schedule PM records for monthly, quarterly,
annualy scheduled dates. Now that the year is ending I would like it to read
the dates for 2007 instead of 2006. I would like to keep the records of
2006. Is there any way I can do it without typing all the dates again for
2007? If possible, I would like to use the same table for 2007.

Are the dates (and any other information) identical except for the year? If
so, a query retrieving all the dates from the existing table, but
substituting 2007 for the year, changed to an Append Query and appended back
to the original table would seem to work.

The DatePart function can be used to extract the Month and Day from the
existing dates to create the new one. Put the "new date creation" in a
function in a standard module and you can use it inside the Query.

Larry Linson
Microsoft Access MVP

 

[JK]

Create an appen query:


INSERT INTO tblSched [ShcedDate], [SomeDesc ]
SELECT DateAdd("yyyy",1,[SchedDate]) AS [NextDate], [tblSched].[SomeDesc]
FROM tblSched
WHERE Year([SchedDate])=Year(Date());

Note the WHERE statement assuming that you are doing the update this year,
if you it in 2007 change the condition to:

WHERE Year([SchedDate])=Year(Date())-1

Regards/JK