Hi,
I’m attempting to query two tables in a single Access 2007 database. Originally, I thought this process would be pretty easy to perform. However, as it turns out, it’s been tremendously more difficult than I anticipated. This forum has been a great resource in designing this query up until this point. However, I’ve come across a stubborn error message that I have been unable to resolve no matter how much research I do here. It is—of course—the dreaded “[font="]Run-time error "3061" Too few parameters. Expected 1”[/font][font="] message.[/font]
I’m using Access 2007 and I’m trying to use the query results to populate a series of Excel 2007 spreadsheets. (It’s actually running in an Excel module. However, since the code in question references a series of Access tables, I thought I ‘d post here. ) The two tables are PS_Stack and PS_Price_Stack. They share two fields in common (STACK_UID and STACK_DATE). By matching the UID’s in the two tables, sorting by STACK_DATE, the resulting query will then export the results into an Excel template.
With all this, I should say I’ve actually benefited greatly from Doug Steele’s posts. He provided an answer to someone else plagued by a similar error message, followed by a useful explanation. However, after unsuccessfully trying to work his solution into my code, I thought I’d post.
Thank you very much for your help.
I’m attempting to query two tables in a single Access 2007 database. Originally, I thought this process would be pretty easy to perform. However, as it turns out, it’s been tremendously more difficult than I anticipated. This forum has been a great resource in designing this query up until this point. However, I’ve come across a stubborn error message that I have been unable to resolve no matter how much research I do here. It is—of course—the dreaded “[font="]Run-time error "3061" Too few parameters. Expected 1”[/font][font="] message.[/font]
I’m using Access 2007 and I’m trying to use the query results to populate a series of Excel 2007 spreadsheets. (It’s actually running in an Excel module. However, since the code in question references a series of Access tables, I thought I ‘d post here. ) The two tables are PS_Stack and PS_Price_Stack. They share two fields in common (STACK_UID and STACK_DATE). By matching the UID’s in the two tables, sorting by STACK_DATE, the resulting query will then export the results into an Excel template.
With all this, I should say I’ve actually benefited greatly from Doug Steele’s posts. He provided an answer to someone else plagued by a similar error message, followed by a useful explanation. However, after unsuccessfully trying to work his solution into my code, I thought I’d post.
Thank you very much for your help.
Code:
Dim sExcelFile As String
Dim sDB As String
Dim objDB As DAO.Database
Dim MyRecordset As DAO.Recordset
Dim MyStackUID As Integer
sDate = Format(dDate, "yyyymmdd")
sExcelFile = e_dir & sDate & ".xls"
sDB = a_dir & sDatabase & ""
Set objDB = OpenDatabase(sDB)
myQuery = "SELECT STACK_UID FROM PS_Price_Stack WHERE POSITION_DATE = #" & dDate & "# ORDER BY STACK_DATE DESC"
Set MyRecordset = objDB.OpenRecordset(myQuery)
' MyStackUID now contains the value that we want to use in our query to the other table
MyStackUID = MyRecordset("STACK_UID")
MyRecordset.Close
objDB.Close
Set objDB = OpenDatabase(sDB)
sExecute = "SELECT PS_Stack.STACK_UID, PS_Stack.HOUR, PS_Stack.STACK_DATE, PS_Price_Stack.POSITION_DATE, PS_Stack.QUANTITY, PS_Stack.PRICE INTO [Excel 8.0;DATABASE=" & sExcelFile & "].[" & sWorksheet & "] FROM [PS_Stack] INNER JOIN PS_Price_Stack ON (PS_Stack.STACK_UID = PS_Price_Stack.STACK_UID AND PS_Stack.STACK_DATE = CStr(PS_Price_Stack.STACK_DATE)) WHERE PS_Stack.STACK_UID = " & MyStackUID & " AND PS_Price_Stack.POSITION_DATE = #" & dDate & "# ORDER BY PS_Stack.STACK_UID, PS_Stack.HOUR, PS_Stack.PRICE"
objDB.Execute sExecute
objDB.Close
Set objDB = Nothing