Select and Update queries / Macro Question (2000)

Topic

New Reply

I have created a Select query to export data from two tables to a spreadsheet using the OutputTo Action in a Macro. The query is named QUARTERLY_EXPORTqry. The only criteria I have set is Between [Enter Start Date, MM/DD/YY] And [Enter End Date, MM/DD/YY] to the Value Date field to specify the date range of the records returned/exported. This query and macro works fine.

I’ve also created a separate Update query to update one field in one of the two tables (DATAtbl.NOT_DATE) that the QUARTERLY_EXPORTqry is tied to. The query is named UPDATE_NOTIFIED_DATEqry and the field to be updated is taken from the QUARTERLY_EXPORTqry. The Update To action is set to Date(). This update query also works fine.

What I’d like to do is that when the operator runs the macro to export the report to Excel, the NOT_DATE field in the DATAtbl table is also updated; however, without having the operator input the date range twice, i.e. the first time when the report is exported to Excel and the second time to run the Update query.

Any suggestions?

Thanks,

Scott

Reply | Quote

Replies

Scott,

Create an unbound form with two unbound text boxes one called start_date and the other called end_date. In both of your queries set the date criteria to “Between [Forms]![your_form]![start_date] And [Forms]![your_form]![end_date]” Also set the format property of the text boxes to short date.

Do not use the ” and where it says [your_form] put the name of the unbound form you created here. Then add a command button to execute your macro. I am assuming the both the OutputTo Action and Update query runs from the same macro.

Winston

Reply | Quote

Thanks Winston. I couldn’t get it to work, i.e. still prompted for dates twice, but I suspect I know why. I forgot to mention that another query sits on top of the QUARTERLY_EXPORTqry query (the query to be exported to Excel), which aggregates data from the first query. It is in this first query that the between start date and end date criteria is set, which may explain why I’m still being prompted twice. At any rate, I’m going to create a separate database to give this a try with some temp data to at least see if I can get it to work.

Thanks for your help.

Reply | Quote