How to set the criteria for archiving data using form-based parameters?

[June7]

No idea, very bizarre as I cannot replicate the issue with your db. Are we following the same steps as shown in my previous post? Have you tested on different computer?

[tim_tims33]

Yes i have. Here is the code i've used... could you check the end bit? (I changed the date field in tblInvoice to InvDate and the date field in tblInvoiceArchive to ArchiveDate)

CurrentDb.Execute "INSERT INTO tblInvoiceArchive([Invoice ID],[Member ID], [ArchiveDate])SELECT [Invoice ID],[Member ID], [InvDate] FROM tblInvoice
WHERE ([InvDate] < # " & (Date - Me.txtDays) & "#)"

When i enter any value from 1 - 10 i get no results appended. However, when i enter 11...then all the records for Nov and Oct appear. Dec ones don't appear until i enter 50 as the number of days!! Interesting..but unsolvable!

thanx june

[June7]

Changing field names was good idea. Although the name ArchiveDate might be misleading as this is still the invoice date, not the date archive took place.

Your code is fine. I made those edits to db and still can't replicate the issue. All 8 records are copied to tblInvoiceArchive. I am using Access 2007, Windows XP.

Try this:

1. Fix the append query qappInvoices with parameter reference to the textbox (copy/paste into SQL View):
INSERT INTO tblInvoiceArchive([Invoice ID], [Member ID], [ArchiveDate]) SELECT [Invoice ID], [Member ID], [InvDate] FROM tblInvoice
WHERE [InvDate] < Date() - Forms!frmARCHIVE.txtDays;

2. code to run the query
DoCmd.SetWarnings False
DoCmd.OpenQuery "qappInvoices"
DoCmd.SetWarnings True

[tim_tims33]

Yes! This works fine..... that's helpful June thanks. I'll probably press on with this solution ...but will keep on eye on this thread if a reason is found why your DML insert didn't work on my db.
You're a legend! cheers

[June7]

It did work in your db, just not on your system under Access 2003. No idea why. I thought Access 2003 had CurrentDb.Execute method available. If the compiler didn't bug on that line, then it should be good code. Very odd.

Another alternative to try:
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblInvoiceArchive([Invoice ID],[Member ID], [ArchiveDate]) SELECT [Invoice ID],[Member ID], [InvDate] FROM tblInvoice WHERE [InvDate]< #" & Date - Me.txtDays & "#"
DoCmd.SetWarnings True