1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2010 Parameter Query Exported to Excel

Discussion in 'Software Development' started by marylizhernandez, Apr 20, 2017.

Thread Status:
Not open for further replies.
Advertisement
  1. marylizhernandez

    marylizhernandez Thread Starter

    Joined:
    Apr 20, 2017
    Messages:
    3
    I'm trying to export a query to excel but the query is geneared based in the following two parameters:

    [Start Date (MM-DD-YYYY)?] DateTime,
    [End Date (MM-DD-YYYY)?] DateTime;

    When I try to run the code below it give me the following error "Run-time error '3061': Too few parameters.Expected 2 , highlighting this line of the code: Set rs = db.OpenRecordset("S QMETRIC - INITIATED - CC VS CM")

    I'm pretty sure that the code is not recognizing these two parameters because when I run the code without using input parameters to filter my query data the code works perfectly, it transfer the data completely. But I need to limit the data to specific date.
    ****************************

    Private Sub Command39_Click()
    Dim x1 As Excel.Application
    Set x1 = New Excel.Application
    x1.Visible = True
    x1.Workbooks.Open ("C:\Users\mhernand\Desktop\QMR GRAPH Template.xlsx")
    Dim ws As Worksheet
    Set ws = x1.ActiveWorkbook.Sheets("CC Vs. CM")
    ws.Select
    Dim db As DAO.Database, rownum As Long
    Set db = CurrentDb
    Dim rs As DAO.Recordset
    Set rs = db.OpenRecordset("S QMETRIC - INITIATED - CC VS CM")
    rownum = 2
    rs.MoveFirst
    Do While Not rs.EOF
    ws.Cells(rownum, 1).Value = rs.Fields("STARTING YEAR ORDER").Value
    ws.Cells(rownum, 2).Value = rs.Fields("Start Quarter").Value
    ws.Cells(rownum, 3).Value = rs.Fields("Total").Value
    ws.Cells(rownum, 4).Value = rs.Fields("Standarized Change Classification").Value
    rownum = rownum + 1
    rs.MoveNext
    Loop
    MsgBox "finished"
    x1.ActiveWorkbook.Close (True)
    x1.Quit
    End Sub
     
  2. Sponsor

  3. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,404
    I would base the records set on query that successfully applies your parameters. Use a between in the criteria. Also the dates can be form driven.
     
  4. marylizhernandez

    marylizhernandez Thread Starter

    Joined:
    Apr 20, 2017
    Messages:
    3
    I'm more looking for what is wrong with code that it transfer the data if there is no parameters to enter in the query and a soon as I add the parameter to be entered, the code does not work.
     
  5. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,404
    I don't understand completely. The code above works until you change the query? What does the query with parameter statement that fails look like? paste SQL view please.
     
  6. marylizhernandez

    marylizhernandez Thread Starter

    Joined:
    Apr 20, 2017
    Messages:
    3
    My main Query: QMR Data - Base

    upload_2017-4-21_18-12-44.png

    The query above feed the following query:

    "S QMETRIC - INITIATED - CC VS CM":
    upload_2017-4-21_18-14-0.png

    When I Run the "S QMETRIC - INITIATED - CC VS CM" Query: I get the following

    upload_2017-4-21_18-15-15.png
    upload_2017-4-21_18-15-36.png

    Once the fields are entered I get the following:
    upload_2017-4-21_18-16-40.png

    And this is the data that I want to transfer to an existing excel file. The code above perform the action if I don't need to enter fields value, but once I add the field values in the query, the code opens the correct excel file but the data is not transferred and I got the following message:

    upload_2017-4-21_18-20-51.png
    upload_2017-4-21_18-23-13.png
     

    Attached Files:

  7. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,404
    I'll agree it looks like your code should work...but since it doesn't I've got a couple of suggestions.

    1. Try entering the date with slash instead of dash? 1/1/2017 (since your query works outside vba this may not work but worth a try)

    2. Since it doesn't like the undefined parameters you could define the parameters with a couple of 'date picker' text boxes on the form then refer to those textbox values in the Between statement. I've done very similar using record set with numbers instead of dates.

    3. Define the date varibles in the vb code and populate on the fly with a strStartDate = (InputBox, "Enter StartDate (MM-DD-YYY): ") This may require a strStartDate format to a date value.

    Since your not doing any data manipulation with the record set you may be able to use this command instead of populating excel with a loop. strQueryName and strXLFile are local varibles. This may allow the query to work as is. Remove all the excel code except filename in this scenario.

    DoCmd.OutputTo acOutputQuery, strQueryName, acFormatXLSX, strXLFile
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1188764