Update a year in a date field

Question

Update a year in a date field

Mark Powell 0

hi i have a access db with a date field that hold data in the following format

02/10/2024 11:01:29

all i want to do is to be able on mass to update anything in the 24 year to 25, is there a way this could be done please?

thanks in anticipation

George Hepworth 22,215 Reputation points Volunteer Moderator

2025-11-30T11:22:30.1366667+00:00

There are several Date functions available in Access. Here is a link to an article describing them.

The one you want here is DateAdd().

But before you go ahead with this plan, it seems like an unusual thing to do in a relational database application. Would you mind explaining what these dates are and why you want to modify them?

2 answers

Your answer

George Hepworth 22,215 Reputation points Volunteer Moderator

2025-11-30T11:22:30.1366667+00:00

There are several Date functions available in Access. Here is a link to an article describing them.

The one you want here is DateAdd().

But before you go ahead with this plan, it seems like an unusual thing to do in a relational database application. Would you mind explaining what these dates are and why you want to modify them?

Answer 1

Viorel 125.6K

For example, create a query using the SQL Query button from Create tab:

UPDATE [MyTable]
SET
    [MyDate] = DateAdd("yyyy", 1, [MyDate])
WHERE
    Year([MyDate]) = 2024

Use the name of your table and field.

Note that 29/02/2024 becomes 28/02/2025.

Answer 2

As George has said, this would be an unusual course of action. Normally where you want to carry rows forward to another year you would execute an 'append' query to insert new rows, amending the value in the date column, e.g. for a payments table. You then do not lose any data:

INSERT INTO Payments(PayeeID, PaymentDateTime, Amount) 
SELECT PayeeID, DATEADD("yyyy", 1, PaymentDateTime), Amount
FROM Payments
WHERE YEAR(PaymentDateTime) = 2024;

This example assumes that the table has an autonumber primary key column, into which values will be inserted automatically, and that PayeeID is a foreign key column referencing the primary key of a Payees table.

Share via

Update a year in a date field

2 answers

Your answer