Date stored in Field minus one year

[Cloonalt]

I need to look at a date stored in a field and subtract one year, so that if 1/1/2004 was stored, my function would produce 1/1/2003. Seems simple, I can't seem to get it. Any help?

Thanks.

 

[PHV]

Take a look at the DateDiff function.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cloonalt]

I've been fooling with it half the morning

 

[PHV]

Sorry for the typo, should be read DateAdd:
newDate = DateAdd("yyyy", -1, oldDate)

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cloonalt]

If the date stored in the field is 01/14/2000, I need the new value to be 01/14/2003, one year prior to the current year, but the same day and month.

Your suggestion gives me one year prior to the field value.

Thanks for your help. Any ideas?

 

[RoyVidar]

Hmm - perhaps the dateserial, using current year-1, month and day from your old date?

[tt]=dateserial(year(date)-1,month(olddate),day(olddate))[/tt]

Roy-Vidar

 

[PHV]

Try something like this:
DateSerial(Year(Date)-1, Month(fldDate), Day(fldDate))

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Cloonalt]

Thanks very much. However, both of those suggestions still produce the year prior to the field date.

My attempt was =Format([startdate],"m/d/" & -1,Now())

but it produces an error

Don't I need -1,now() to get the year prior to the current year?

 

[BobJacksonNCI]

Sorry for protruding...

now() includes date and time

date() includes date only

Access has a tendency to strip out () after it has been entered...

=dateserial(year(date())-1,month(olddate),day(olddate))

HTH,
Bob

 

[Cloonalt]

Grateful for all protruders

=dateserial(year(date())-1,month(olddate),day(olddate))

works

Thank you!