Adding a year to a date using Year() in update query

[Guest]

Hi there,

Seem to be having a issues with using an update query to change the year of
a date in a table.

For example the date is:
31/01/2006

My update query is using the following expression in the 'update to' section:
Year([MOT Expiry Date])+1

With Criteria:
<Date()

This should change the year of any record in the field 'MOT Expiry date' if
the date in the field is after today but all i get is a totaly random date
(ie 25/06/1950).

There must be something i am easily missing but i have been trying to get
this to work in so many different ways and for so long that i am all but
ready to give up.

Any advice would be great!
Thanks loads,
Davey

 

[fredg]

Hi there,

Seem to be having a issues with using an update query to change the year of
a date in a table.

For example the date is:
31/01/2006

My update query is using the following expression in the 'update to' section:
Year([MOT Expiry Date])+1

With Criteria:
<Date()

This should change the year of any record in the field 'MOT Expiry date' if
the date in the field is after today but all i get is a totaly random date
(ie 25/06/1950).

There must be something i am easily missing but i have been trying to get
this to work in so many different ways and for so long that i am all but
ready to give up.

Any advice would be great!
Thanks loads,
Davey

If the date in the field was 31/01/2006, your expression would return
a value of (2006 + 1) = 2007, which, as a date value, is 29/06/1905.

DateAdd("yyyy",1,[[MOT Expiry Date])
will add one year to whatever the date field is.

A criteria of <Date() will affect only records before today, not
after.