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

[tim_tims33]

Hi,

I'm trying to create an archiving system, where i use a simple Append Query followed by a Delete Query.

A typical criteria for the Append Query is less than Date()-30...so any records older than 30 days can be appended to an archive table. This works fine when i enter it in the Query Design criteria row.

But, I would like to make this user-defined. I have set up an unbound form as shown in the first attachment...and made a global variable entitled 'ArchiveDays'. I am hoping to use the variable to act as the criteria for the append criteria. (Please note that in the screendump...they can select an option button if they just want to stick to 1 month old. I also show you my assignment operations there).

My question is... how do i get the variable 'ArchiveDays' value to be the criteria for my append query?

Many thanks

tim

[June7]

Construct and run the sql statement with concatenated variable in VBA code. Something like:

CurrentDb.Execute "INSERT INTO tablename(field1, field2) SELECT field1, field2 FROM tablename WHERE datefield< " & Date()-Me.textbox

Why do you need to 'archive' records by moving to another table - why not just filter records on the same date field or set a 'Status' field value and filter on that?

[tim_tims33]

Tried that, but it isn't working for me. Should I be looking at DAO and learning how to work with this?? (Iv never used / learnt DAO)

Thx. Tim

[June7]

Why doesn't it work - error message, wrong results, nothing happens?

I forgot, probably need delimiters for the date value:

WHERE datefield< #" & Date()-Me.textbox & "#"

It should work. Show your exact code. Provide db if you want - follow instructions at bottom of my post.

Again, why do you need to do this as opposed to just filtering records?

[tim_tims33]

Hi June,

First of all, many thanks for your help on this one. I know what you mean by saying that data should be in one table and from there, it should be 'filtered'. However, and this may sound silly.... i wanted an excuse to use Append and Delete queries.
It was my ambitition that the Append and Delete queries would, when run by the macro, check the parameters entered/selected on frmArchive.

(I realise now, that if I get a VB solution working...there will be no need for teh Append and Delete Q...and Macro!???)

I've attached the database. Please look at frmArchive where you will find the facility that my user uses to decide what to 'Archive'.

(If you want, you can look at my frmSearch...which i'm modestly proud of...as an example of what i would like to achieve...but for archiving)
Any help is always appreciated. Thanks.

Look forward to hearing from you!

Tim

[June7]

Bad excuse for use of Append and Delete queries if this is intended to be an operational database and not just a learning tool.

VBA does not negate the need for Append and Delete SQL actions, just don't need saved Access query objects. The basic premise of what you want and the argument against it are same.

If you want to 'archive' records from tblInvoice then also need to 'archive' from tblInvoiceDetails?

[tim_tims33]

Yeah...I wanted to try and get Invoice archived first...before attempting InvoiceDetails. I suppose InvoiceID would have to be stored as some kind of Global variable.

[June7]

I just tested your append query and it worked. But the VBA SQL is wrong, use:

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

When names include spaces, special characters, punctuation (underscore is exception) or are reserved words, must enclose in []. As noted earlier, needed # delimiters for date value.

Set DefaultValue (maybe 15) for txtDays so the SQL won't error if value not input.

Also need code to set the txtDays value if 1 Month button is clicked. What will be '1 Month' - 30 days?

[tim_tims33]

Yeah, 1 month should be 30 days. I will try ur ammendments and test it. Many thx for this June. How would InvoiceDetails work then? It has no date field, so I wud have to use the InvoiceID field to append it to InvoiceDetails archive? Will try this now. Thx again. Tim

[June7]

Yes, will need some criteria to base the InvoiceDetail archive on. Perhaps the maximum InvoiceID in the archive after the APPEND action?

[tim_tims33]

Hi June,

I tried that VBA and it works. However, when i test it...i get unexpected results. For example, if i type 1 into txtDays...(1 day)...then i would expect to see ALL invoices appended to tblInvoiceArchive. however, i only get ones for the months of Nov and Oct. Why is this?

Also, is the statement the same for a 'delete action' .... DELETE FROM ... rather than INSERT INTO... so i can delete the same records from the original tblInvoice?

Thanks...

[June7]

I tested with 1 day criteria and it worked in the downloaded db. Can't replicate the issue.


CurrentDb.Execute "DELETE FROM tablename WHERE ..."

[tim_tims33]

It's still not giving me all the invoices when i enter 1 into txtDays. I've attached the uptodate DB so you can try it. No data for December seems to show up at all for some reason? Unless the number is affecting the month?

Here it is...(go to frmARCHIVE)

thx,tim

[June7]

I download latest db, opened frmARCHIVE, selected Other, entered 1 for days, clicked Run. All 8 records were posted to the Archive table.

[tim_tims33]

Hi June,

This is really annoying me. Have been fiddling about with this for the last hour and I still only get a few records archived! In fact, if i have December Invoices...they don't even appear! I have checked my computer's date is correct. Any ideas why ALL records are not appearing when i just enter '1' into me.txtdays??
thx, tim