Record Copy

[Douglas J. Steele]

OldSQL = "INSERT INTO C:\History\Old.Mdb\tblChecksTMP " & _
"SELECT * FROM tblChecks " & _
"WHERE tblChecks.BizDay <= DateAdd("yyyy", -1, Date());"
DoCmd.RunSQL OldSQL

NOOldSQL = "DELETE * FROM tblChecks " _
& "WHERE tblChecks.BizDay <= DateAdd("yyyy", -1, Date());"
DoCmd.RunSQL NOOldSQL

Personally, I prefer using

CurrentDb.Execute SQL, dbFailOnError

rather than

DoCmd.RunSQL SQL

for two reasons. The Execute method will raise a trappable error if
something goes wrong with the query, and it doesn't have the "Access is
about to...." pop-up message.

 

[DS]

I want to move any file from one database to another that is older than
one year. What is the syntax for this?

OldSQL = "INSERT INTO C:\History\Old.Mdb\tblChecksTMP " & _
"SELECT * FROM tblChecks " & _
"WHERE tblChecks.BizDay = Date()-365;"
DoCmd.RunSQL (OldSQL)

NOOldSQL = "DELETE * FROM tblChecks " _
& "WHERE tblChecks.BizDay = Date()-365;"
DoCmd.RunSQL (NOOldSQL)

-365 days doesn't seem right?
Also I'm moving it to another database.

Thanks
DS

 

[DS]

OldSQL = "INSERT INTO C:\History\Old.Mdb\tblChecksTMP " & _
"SELECT * FROM tblChecks " & _
"WHERE tblChecks.BizDay <= DateAdd("yyyy", -1, Date());"
DoCmd.RunSQL OldSQL

NOOldSQL = "DELETE * FROM tblChecks " _
& "WHERE tblChecks.BizDay <= DateAdd("yyyy", -1, Date());"
DoCmd.RunSQL NOOldSQL

Personally, I prefer using

CurrentDb.Execute SQL, dbFailOnError

rather than

DoCmd.RunSQL SQL

for two reasons. The Execute method will raise a trappable error if
something goes wrong with the query, and it doesn't have the "Access is
about to...." pop-up message.

Great thanks Douglas. The yyyy thing seems better than -365 days!
Will this work on a leap year?

Thanks
DS

 

[Douglas J. Steele]

Great thanks Douglas. The yyyy thing seems better than -365 days!
Will this work on a leap year?

Yes.