Calculate "two years ago"

[Big Pat]

Hi,

I have a calculated field on my form which checks for the date of the last record in the database. (For ease, EVERY record is a 1st of month date.)

Now I need another calculated field which gives me the first of the month for "2 years ago, but one month on" if that makes any sense.

For example, just now, the last record in the database is 01/12/2007 (UK date format... 1st Dec) so I want the calculated field to return 01/01/2006 (1st Jan).

Next month, my last record in database will read 01/01/2007 and I will want the new field to return 01/02/2006. This will be useful in always providing a rolling two years worth of data which is what i need for further work.

I thought it should be something like
=DateSerial(Year([Latestdate])-1),Month([Latestdate])+11,1)

but I can't get it to work. Help!

 

[rainman89]

do get the date 2 years ago

tempdate=dateadd("y",-2, [yourdatefield])

newdate=dateadd("m",1, [tempdate])

 

[Big Pat]

Thanks Ray

I tried that but it's returning 29/11/2007. How it gets to 29th Nov is beyond me!

My [latestdate] field is =DMax("[Date]","tblAntibioticsFromPharmacy") and I have set its format as mmm yyyy, so it displays Dec. 2007

I changed that format to read dd/mm/yyyy and it correctly displayed 01/12/2007, but the new field still returned 29/11/2007.

Any other ideas?

 

[rainman89]

Thanks Ray

I tried that but it's returning 29/11/2007. How it gets to 29th Nov is beyond me!

My [latestdate] field is =DMax("[Date]","tblAntibioticsFromPharmacy") and I have set its format as mmm yyyy, so it displays Dec. 2007

I changed that format to read dd/mm/yyyy and it correctly displayed 01/12/2007, but the new field still returned 29/11/2007.

Any other ideas?

im sorry it was dateadd ("yyyy", -2,[yourdate])

 

[Big Pat]

Close, but no cigar (yet!)

That returns 01/12/2005, which is taking two years from that [latestdate]. But I need to get to the beginning of the next month after that, so I need 01/01/2006. Sorry if that wasn't clear. I need to subtract two years and add a month, or subtrcat 13 months if such a thing is possible.

 

[Brianwarnock]

What's wrong with Dateadd("m",-23,[yourdatefield])

Brian

Edit yes its possible, our posts crossed, but minus 2 years + 1 month is minus 23 months!!

 

[MaryIrl]

Dateadd("m",-23,[datefield])

Oops, got there before me.

 

[rainman89]

What's wrong with Dateadd("m",-23,[yourdatefield])

Brian

Edit yes its possible, our posts crossed, but minus 2 years + 1 month is minus 23 months!!

right but now how do we get the first of this new month? thats where im getting stumped

 

[Big Pat]

I've got it to work for this month by using

=DateSerial(Year([latestdate])-1,Month([latestdate])-11,1)

but that wont work next month, when the latestmonth will be 1 and the function will try to evaluate -10 in the month part.

 

[Brianwarnock]

As I understand it we all ready have the first of the old month so won't it be automatic?

Brian

 

[Big Pat]

Brian,

Yours worked. DateAdd isn't a function I was familiar with (having inherited this stuff from someone else) But it was that easy. Top man!

I can go home happy (having saved this!!)

 

[Big Pat]

Thanks to ALL of you. This saves work on my part. Much clicking of scales to do now!