Rename Table - Append Month 2 months previous to title 2

[dominicdunmow]

Hi there,

I have a table that I'd like to archive, in order to do so I'm renaming it and adding appending the month in which it applies to. The code below adds the current month, but I'd like to add 2 months previous, e.g Format(Date, "MMM" - 2)
but I can't work out the code.

DoCmd.Rename "tblArchiveOSP_" & Format(Date, "MMM"), acTable, "tblOSPPrevious"

Can anyone point me in the right direction please.

Thanks

 

[SkipVought]

Hi,

The YEAR is not important? So this year's Sep table will go bye bye when Oct 2006 rolls around?

DoCmd.Rename "tblArchiveOSP_" & Format(DateSerial(Year(Date),Month(Date)+1,1), "MMM"), acTable, "tblOSPPrevious"

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[PHV]

DoCmd.Rename "tblArchiveOSP_" & Format(DateAdd("m",-2,Date), "MMM"), acTable, "tblOSPPrevious"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dominicdunmow]

Thanks for your responses.

The database is a temporary solution so in theory year doesn't matter but for best practice's sake I've included - thanks for the nudge.

DoCmd.Rename "tblArchiveOSP_" & Format(DateAdd("m", -2, Date), "MMM") & Format(Date, " yyyy"), acTable, "tblOSPPrevious"

Dominic

 

[SkipVought]

For collating purposed, I'd format the YEAR first...
[tt]
Format(DateAdd("m", -2, Date), "yyyymmm")
[/tt]


Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]