Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select and Update queries / Macro Question (2000)

    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

  2. #2
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Anderson, Indiana, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select and Update queries / Macro Question (2000)

    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

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select and Update queries / Macro Question (2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •