VBA or macro to conditionally run append query

[aero]

I am able to query the last two weeks and a local table of the remaining 2 years worth of data as to not bog down the server. The only way I idea I have come up with for doing this is to create an append query that acts conditionally on a query that determines whether the local table has the strings "yymmdd" of the last two report periods by comparing the results of two Max(report periods column)s for the selected ID. If they aren't equal, than the append query should append the most recent week or two of data to the local table. At this point I would base my pivot table and pivot chart off of only the the local table. The problem is, I don't know the vba to tell access to compare the result of the Max() functions from the two queries I run on the the local archive, and live table on the server. Can anyone help? There is also the issue that although their may be a value in the local table for the most current two weeks, it might be updated in the server based table. Then I might have to go about using an update query without knowing what the Primary ID of the last two weeks are, but that's a smaller issue and will be encountered less often. Anyone able to help????!

[ranman256]

On the button click , get the max date , put on form, then run the query to read the date off the form...

vDate = Max("[RunDate]",table)

txtMaxDate = vDate
docmd.openquery "qaAddRecords"       '

'your append query will use the criteria for date on the form to pull the records....forms!frmMyForm!txtMaxDate
'it is faster than using Max in the actual query.

[aero]

I'm not sure what you mean not to run the Max function in a query. If I do it the way you've written it it looks like it's running a max on one field in the table from the server because it will return the max date associated with all Respondent IDs rather than the one I have selected on my form. Currently I'm running a max query based on the selected ID on my form first. Does that not need to be done?