Query Records since last run

[Graeme]

I am trying to do what I thought would be simple, but am finding it
difficult.
I want to write a query which extracts all records from a table suign a
date criteria. I want to store this date somewhere, and then next time
I run the same query, I want to extract only records since the last
time the query was run.

e.g - first time I run the query (on 06/08/05) I extract these.

Customer Sales Date Sales Value
AB001 05/08/05 12.00
AB003 05/08/05 18.20
AB001 06/08/05 13.00


Next Time I run the query, (on 08/08/05) the 'Sales' Table contains

Customer Sales Date Sales Value
AB001 05/08/05 12.00 Not needed
AB003 05/08/05 18.20 Not needed
AB001 06/08/05 13.00 Not needed
AB001 07/08/05 17.65 << want this
AB001 07/08/05 13.55 << Want This
AC004 08/08/05 25.00 << Want This

I.e. I only want to extract records added to the sales table SINCE
06/08/2005,

I cannot 'prompt' for the last-run date, since I am trying to automate
this query to ru overnight (using Macro's and Windows Scheduler)

NOTE: all the dates I illustrate are UK format, ie. (dd/mm/yy)
Any idea's ?
all help gratefully appreciated.

 

[Guest]

You need to build a table named LastExtract and have a DateTime field. Load
in your earliest date.
Use code or macro to do the following actions.
Open a form (1) with LastExtract as record source.
Run your select query with criteria
[Forms]![YourFormAbove]![YourTextBoxDateTimeField].
Run an update query named LastExtraction. Have it to update the DateTime
field in LastExtract with Now().
Close form (1).

If you think you might mess up and miss some data you can use an append
query instead of an update and have the form (1) pull last date.

 

[[MVP] S.Clark]

Store the value in a table, text file, registry setting, or global variable.
Pick anyone, and retrieve it before or during the query.


--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[Graeme]

Thanks guys; I'd sort-of got there anyway in that I realised I needed
to save the date 'Today' into a table using an UPDATE Query after my
extraction - could't work out how to get the date out of this table and
into a query, but I never thought of using a FORM, and then pulling
from the FORM into the Query.

I'll give it a go. Thanks Again.