?
Solved

exporting select query to excel without reentering parameters

Posted on 2003-03-24
7
Medium Priority
?
377 Views
Last Modified: 2009-12-16
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
Comment
Question by:jnault
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 5

Expert Comment

by:funke
ID: 8198282
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
 
LVL 5

Expert Comment

by:funke
ID: 8198288
then you can set your export variable to this "NewQuery"
0
 
LVL 4

Expert Comment

by:dotthei
ID: 8202033
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:jnault
ID: 8203095
funke - could you spell that out for me a bit more, I am not at all familiar with SQL syntax. thanks
0
 

Author Comment

by:jnault
ID: 8203192
funke - could you spell that out for me a bit more, I am not at all familiar with SQL syntax. thanks
0
 

Author Comment

by:jnault
ID: 8210935
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
 
LVL 4

Accepted Solution

by:
dotthei earned 200 total points
ID: 8211146
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question