exporting select query to excel without reentering parameters
Posted on 2003-03-24
I have a select query (Access 2002) with parameters of the month and year. (I am a VBA, SQL noob fyi) When run, the query prompts you for the month, then the year, and pulls all the matching records and displays them in a particular form. I am using this as the select criteria in the 'date' field in the db:
Like [Enter month of expiration in numeric format] & "/" & "*" & "/" & [Year of Expiration in 4 digit format:]
I have two related questions - the first is, can i set this up to input the criteria in MM/YYYY format instead of entering them in seperate pop up boxes?
Second question: Once the query is run, and the form is displayed, there is a command button that the user can click to export the query information to an excel spreadsheet. This is what I am using now to do this:
DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLS, "Excel File.XLS", False
When this is run, the user needs to re-enter the query parameters again. I haven't been able to figure out a way to output the query that was just run - it re-runs the query and needs the information from the user again. The form that the information is displayed in doesn't contain all the fields that need to be exported, so outputing the form with the current recordset wouldn't work well. I am sure there is a relatively easy way to do this, but so far haven't had any success.
Thanks for any and all help