How to change year digit in update query?

[Osman]

Hello,

I have date field in an Access table.
I want to change only the year of that field using an update query.

Example:
10.10.2008 my date value

I want to pass the current year 2010 to that field and
I want it to be 10.10.2010.



The month and day digits will not change. But the year will change every year according to the current year like below :
10.10.2010
10.10.2011
10.10.2012

Thank you

[jzwp11]

Welcome to the forum!

If the field is a date/time datatype, they you can use the dateadd() function with the year option. If all of the dates are 2008 then to update them to 2010, the dateadd() function would look like this:

dateadd("yyyy",2, myDateField)

The query might look something like this (you will have to substitute your own table and field names)

UPDATE Table1 SET Table1.myDateField = DateAdd("yyyy",2,mydatefield);

[Osman]

Thank you for your welcome message.

dateadd("yyyy",2, myDateField)

I know dateadd function. But the years are not only 2008. They vary.
That's why, I can't use fixed numbers.

[jzwp11]

You can use an expression instead of the 2 within the dateadd() function that yields the correct number of years to add

dateadd("yyyy", 2010-year(mydatefield), myDateField)


Out of curiosity, you said this:

But the year will change every year according to the current year like below :

Can you explain why you need to do this? What do the dates represent?

[jzwp11]

To make the expression even more versatile, you can modify the expression for the current year:

dateadd("yyyy", year(date())-year(mydatefield), myDateField)

[Osman]

dateadd("yyyy", year(date())-year(mydatefield), myDateField)

This solved my problem. Thank you very much indeed.

[jzwp11]

You are welcome. Good luck with your project.