• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

exporting select query to excel without reentering parameters

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
0
jnault
Asked:
jnault
  • 3
  • 2
  • 2
1 Solution
 
funkeCommented:
Create a duplicate query and set the built SQL statement with the where clauses to the this query...

SQL = "Select * from .. wHERE x like..."
Currentdb.QueryDefs("NewQuery").SQL = SQL
0
 
funkeCommented:
then you can set your export variable to this "NewQuery"
0
 
dottheiCommented:
re:   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?

yes - the query object has an inputmask property - define your input mask as desired
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jnaultAuthor Commented:
funke - could you spell that out for me a bit more, I am not at all familiar with SQL syntax. thanks
0
 
jnaultAuthor Commented:
funke - could you spell that out for me a bit more, I am not at all familiar with SQL syntax. thanks
0
 
jnaultAuthor Commented:
Well I solved my own problem using a different method (maybe bulkier than needed). If anyone's curious, this is what i did:

Instead of using pop up windows for the data entry for the select query, I created a form to input the two date criteria (with drop down boxes). This form is linked to a table with 2 fields, month and date. The query is now linked to this table as the criteria. The table is cleared with a delete query after this process is done, so the table doesnt fill up.

None of the comments i got really qualify as answers, so i am hesitant to award points...
0
 
dottheiCommented:
Someone once said - "A good plan today is better than a perfect plan tommorrow."

The bottom line in developing any tool is - "does it do what i need it to do?"

Best wishes
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now