Macro to seperate months (?) (1 Viewer)

[Mud24]

I am working on a DB that records info monthly. I want the DB to do is; Store the previouse months info, clear the table so that the new months info can be inserted.

I would also need the DB to store certain statistical data so that I can refer back to it and print out monthly reports. Can ayone help?

 

[Travis]

Use an Append Query to store the previous data to its new resting place and a Delete Query to remove it from its old home. Use the Macro's OpenQuery. You will need to set the warnings to off and the beginning of the Macro and back on at the end.

or you can make this into a Public Sub by doing this:

Public Sub SetNewMonth()
dim iPreMonth
iPreMonth = DatePart("mm",Date)-1
Currentdb.Execute("INSERT INTO [Table1] ( id, Test, DateField ) SELECT Table2.id, Table2.Test, Table2.DateField FROM [Table2] Where Month = " & iPreMonth & ";")
currentdb.execute("Delete * From Table2 where Month = " & iPreMonth & ";")
end sub

Your choice.