query date help

[lookforsmt]

Hi!
I have field name DispatchDate which is in the format as dd/mm/yyyy
which I want to write a query to reduce the date by 3 years

For example: 09/04/2015 will be read as 09/04/2012

I am using the below query but it only changes the day
09/04/2015 changed to 06/04/2014

ChangedYear: [CaptureDate]-3

can anyone help me to get the year changed only
thanks

 

[CJ_London]

use the dateadd function

ChangedYear: dateadd("yyyy",-3,[CaptureDate])

which will automatically take care of leap years

see this link for more info

https://www.techonthenet.com/access/functions/date/dateadd.php

 

[lookforsmt]

Thanks CJ for the query. It is changing the year to less 3 year now.

But I must apploigize my question was partially wrong. What I wanted to ask was, irrespective of the year2015 , it should reduce the year by 3. as shown in the below example.

CaptureDate ChangedYear
09/04/2015 09/04/2012
09/04/2016 09/04/2012
09/04/2017 09/04/2012

so this will also take care of the leap year where 29/02/2016 would be read as 20/02/2012

I guess this has to be written by function code or query, either way I have zero knowledge in both

Sorry for the partial question raised earlier.
Thanks

 

[lookforsmt]

Hi!

kindly read the line in the quote as 29/02/2012

so this will also take care of the leap year where 29/02/2016 would be read as 20/02/2012

 

[MarkK]

... irrespective of the year2015 , it should reduce the year by 3. as shown in the below example.

CaptureDate ChangedYear
09/04/2015 09/04/2012
09/04/2016 09/04/2012
09/04/2017 09/04/2012

I don't understand this rule. What does 'irrespective of the year 2015' mean?

 

[lookforsmt]

Thanks Mark for the response
what I meant was the captured date, irrespective year 2015, 2016, 2017, etc

CaptureDate ChangedYear
09/04/2015 09/04/2012
09/04/2016 09/04/2012
09/04/2017 09/04/2012

the ChangedYear should should allways be 2012

hope I was clear

thanks

 

[MarkK]

So this is solved then?

 

[lookforsmt]

Thanks Mark, this is still not resolved

 

[bob fitz]

Perhaps:
ChangedYear: Left([CaptureDate],6) & "2015"

 

[CJ_London]

are you saying you want to substitute the year with 2012

Also this

so this will also take care of the leap year where 29/02/2016 would be read as 20/02/2012

No it will change 29/02/2016 to 28/02/2013

 

[lookforsmt]

Thanks Bob, the query expression has worked for me.
I want to thank each and everyone here for their time and support to help me to through.

Thanks CJ, that was typo error, it was to be read as 29/02/2012. but I would take even 28/02/2012 also, since this date would also be okay for my project as long as the year is 2012.
Thanks again