Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access database runtime error 3061: too few paremeters. Expected2

Posted on 2007-03-28
3
Medium Priority
?
276 Views
Last Modified: 2010-05-18
In Access database I am getting an error, when I run a query I get  runtime error 3061 too few parameters Expected2.
I get the error when the query gets to this line:  DBS.Excute (myguery)
Will appreciate all the help I can get

Vidia
0
Comment
Question by:vidiag
[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
  • 2
3 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 18811996
Usually that results from a typo in your query text.  Look for an incorrect field name or table name in the query.  A typo like that can be interpreted as an unmatched parameter and produce that error.  
0
 

Author Comment

by:vidiag
ID: 18844533
I don't know where the error is, I will post the query
Option Compare Database
Option Explicit
Function sale()
Call Death Row
End Function
Sub Death_Row()
Dim DBS As -Database
Dim qdf As QueryDef_
Dim main, mainstr, main2, recnew As Recordset
Dim MYDEL As String
Dim.i As Integer
Dim myquery As String, Ans, str1 As String
DiM NewName As String
Dim. outfile As String
Dim costfile, str As String
Set DBS CURRENTDB
'NewName = InputBox("Please Enter the Monthly Sales File to Copy", "RLM SA LES")
'costfile = InputBox("Please Enter the Cost file you would like to use", " RLM SALES")
'DoCmd.DeleteObject acTable, "CRLMSALES"
NewName = Forms!FRM DOWNLOADS!Text45
costfile = Forms!FRM DOWNLOADS!Text47
DoCmd.Rename "COST", acTable, costfile
'DoCmd.CopyObject , "Cost", acTable, costfile
str = "DELETE HIGDON SALE.STYLE, HIGDON SALE.CUST, HIGDON SALE.CUSINME, "
str str & "HIGDON3ALE.SLMNO HIGDONISALE.DIV, HIGDON aALE.SLS, HIGDON_SALEE .PCS, "
str = str & "HIGDON SALE.COST, HIGDON SALE.DISC, HIGDON SALE.RYLT_C, HIGDON SA LE.orgp.rc, "
str = str & "HIGDON SALE.SELLPRC, HIGDON SALE.Period "
str = str & "FROM HIGDON SALE;"
DBS.Execute (str)
'DoCmd.OpenQuery "delete old records", acViewNormal
DoCmd.Rename "CRLMSALES", acTable, NewName
- 'DoCmd.CopyObject , "CRLMSALES", acTable, NewName
MYDEL "DELETE [HIGDONSALES-1].SLM015, [HIGDONSALES-1].DIV015, [HIGDO NSALES-1].Div Name, [HIGDONSALES-1].STYLE, [HIGDONSALES-1].Sum0fPCS, [HIGDONSA LES-1].SELPRC7 [HIGDONSALES-1].SALES, [HIGDONSALES-1].SLNAME, [HIGDONSALES-1]. OFFPRICE, [HIGDONSALES-1].FULLPRICEi [HIGDONSALES-1].orgprc, CHIGDONSALES-11.0 ustNo, [HIGDONSALES-1].CUSTNAME FROM [HIGDONSALES-1];"
DBS.Execute (MYDEL)
'Set qdf = dbs.CreateQueryDef("ZZZ", MYDEL)
'DoCmd.OpenQuery "ZZZ"
'DoCmd.DeleteObject acQuery, "ZZZ"
'CREATE HIGDONSLES 1..TABLE FOR USE WITH OTHER QUERIES
'myquery      "INSERT INTO [HIGDONSALES-1] ( SLM015, DIV015, Div Name, ST
YLE, " 
'myquery myquery & "Sum0fPCS, SELPRC,•SALES, SLNAME, OFFPRICE, FULLP RICE, orgprc, "
'myquery myquery & "CustNo, CUSTNAME ) SELECT [QRY_SALES OFF&REG-2]. 8LM015, "
'myquery = myquery & "[QRY_SALES_OFF&REG-2].DIV015, Division.Div_Name,
'myquery = myquery & "[QRY_SALES OFF&REG-2].STYLE, WRYLSALES OFF&REG-
_
2].Sum0fPCS, "
'myquery = myquery & "[QRY SALES OFF&REG-2].SELPRC, [ORY SALES OFF&REG -2].SALES, "
'myquery = myquery & "(QRY_SALES_OFF&REG-2].SLNAME, IIf([SELPRC]<([ORG PRC]*0.8),"
'myquery = myquery & "[SALES],0) AS OFFPRICE, IIfUSELPRC]<(0.8*(ORGPR
 
C]),0,[-SALES]) AS FULLPRICE, "
'myquery = myquery & "[QRY_SALES_OFF&REG-2].orgprc, [QRY_SALES_OFF&REG -2].CustNo, "
'myquery = myquery & "[QRY_SALES_OFF&REG-2].CUSTNAME FROM HORY_SALES_ OFF&REG-2] "
'myquery = myquery & "LEFT JOIN Division ON [QRY_SALES_OFF&REG-2].DIVO 15 = Division.Division)      "
'myquery      myquery & "LEFT JOIN TBL SALESMAN NAME ON WRY SALES_ OFF&RE
G-2].SLM015 = TBL SALESMAN NAME.SLMNO;"
myquery = "INSERT INTO [HIGDONSALES-1]      ( SLM015,      DIV015,      Div_Name, STYLE,
Sum0fPCS,      "
myquery      myquery      &      "SELPRC, SALES, SLNAME, orgprc, CustNo, CUSTNAME, OFF
PRICE,      "
myquery =      myquery      &      "FULLPRICE )"
myquery =      myquery      &      " SELECT CRLMSALES.SLM015, CRLMSALES.DIV015,      "
myquery      myquery      &      "CRLMSALES.NAME00 AS Div Name, CRLMSALES.STY015 AS ST
YLE,      "
myquery = myquery      &      "Sum(CRLMSALES.PCS) AS SumOfPCS, CRLMSALES.OVP015 AS
SELPRC,      "
myquery = myquery      &      "Sum([PCS]*[SELPRC]) AS SALES,      TEL_ SALESMAN NAME.SLNA
ME, "
myquery = myquery & "ORGPRICE.LastOfSTP622 AS orgprc, CRLMSALES.ACT015 AS CustNoi "
myquery = myquery & "CRLMSALES.NAM201 AS CUSTNAME,. "
myquery      myquery & "IIf([0VP015]<([ORGPRC]*0.8),[SALES],0) AS OFFPRICE,
myquery =.myquery & "Iif([0VP015]<t0.8*[ORGPRC]),0,ISALES]) AS FULLPRICE" myquery      myquery & " FROM ((CRLMSALES LEFT JOIN ORGPRICE ON CRLMSALES.ST
Y015 =-"
myquery = myquery & "ORGPRICE.STY622) LEFT JOIN TBL SALESMAN NAME ON " myquery      myquery & "CRLMSALES.SLM015 = TBL SALESMAK NAME.SLRNO)      LEFT JOI
N Division "
myquery = myquery & "ON CRLMSALES.-DIV015 = Division.Division "
myquery      myquery & " GROUP BY CRLMSALES.SLM015, CRLMSALES.DIV015, CRLMSA
LES.NAME00, "
myquery = myquery & "CRLMSALES.STY015, CRLMSALES.OVP015, TBL SALESMAN NAM E.SLNAME, "
myquery = myquery & "ORGPRICE.LastOfSTP622, CRLMSALES.ACT015, CRLMSALES.N AM201, "
myquery = myquery & "ORGPRICE.LastOfSTP622, [emo015] & ' - ' & [eyr015],
myquery = myquery 4 "Division.Div_Name"
myquery = myquery & " ORDER BY CRLMSALES.SLM015, CRLMSALES..STY015;" 'Set qdf = dbs.CreateQueryDef("HIGDON SALES-1", myquery) 'DoCmd:OpenQuery "HIGDON_SALES-1"      7 -
'DoCmd.DeleteObject acQuery, "HIGDON_SALES-1"
      DBS.Execute      (myquery)
0
 
LVL 42

Accepted Solution

by:
dqmq earned 1500 total points
ID: 18846069
You have so many problems in that code, I hardly know where to start.  First, I see three queries, so take them one at a time.  
----------------
str = "DELETE HIGDON SALE.STYLE, HIGDON SALE.CUST, HIGDON SALE.CUSINME, "
str str & "HIGDON3ALE.SLMNO HIGDONISALE.DIV, HIGDON aALE.SLS, HIGDON_SALEE .PCS, "
str = str & "HIGDON SALE.COST, HIGDON SALE.DISC, HIGDON SALE.RYLT_C, HIGDON SA LE.orgp.rc, "
str = str & "HIGDON SALE.SELLPRC, HIGDON SALE.Period "
str = str & "FROM HIGDON SALE;"
DBS.Execute (str)

Above query deletes everything from HIGHDON table and can be simplified to:
DBS.Execute("DELETE HIGHDON")

---------------------
MYDEL "DELETE [HIGDONSALES-1].SLM015, [HIGDONSALES-1].DIV015, [HIGDO NSALES-1].Div Name, [HIGDONSALES-1].STYLE, [HIGDONSALES-1].Sum0fPCS, [HIGDONSA LES-1].SELPRC7 [HIGDONSALES-1].SALES, [HIGDONSALES-1].SLNAME, [HIGDONSALES-1]. OFFPRICE, [HIGDONSALES-1].FULLPRICEi [HIGDONSALES-1].orgprc, CHIGDONSALES-11.0 ustNo, [HIGDONSALES-1].CUSTNAME FROM [HIGDONSALES-1];"
DBS.Execute (MYDEL)

Above statement has typos.  First, it should start "MYDEL=".  Second, there are extra spaces in some of the names: [HIGDO NSALES] and [HIGDONSA LES]. Third, column names containing spaces, must be bracketed. so, possibly "[HIGHDONSALES].[Div_Name]" will work better.  In fact, that is exactly the kind of problem that will cause the error you reported because the query mistakenly thinks "Div" is a column name.
I'm still going: What is FULLPRICEi?  Where did CHIGHDONSALES-11.0 come from?  "ustNo" looks suspicious.  

---------------------
myquery = "INSERT INTO [HIGDONSALES-1] ( SLM015, DIV015, Div_Name, STYLE,
Sum0fPCS, "
myquery myquery & "SELPRC, SALES, SLNAME, orgprc, CustNo, CUSTNAME, OFF
PRICE, "
myquery = myquery & "FULLPRICE )"
myquery = myquery & " SELECT CRLMSALES.SLM015, CRLMSALES.DIV015, "
myquery myquery & "CRLMSALES.NAME00 AS Div Name, CRLMSALES.STY015 AS ST
YLE, "
myquery = myquery & "Sum(CRLMSALES.PCS) AS SumOfPCS, CRLMSALES.OVP015 AS
SELPRC, "
myquery = myquery & "Sum([PCS]*[SELPRC]) AS SALES, TEL_ SALESMAN NAME.SLNA
ME, "
myquery = myquery & "ORGPRICE.LastOfSTP622 AS orgprc, CRLMSALES.ACT015 AS CustNoi "
myquery = myquery & "CRLMSALES.NAM201 AS CUSTNAME,. "
myquery myquery & "IIf([0VP015]<([ORGPRC]*0.8),[SALES],0) AS OFFPRICE,
myquery =.myquery & "Iif([0VP015]<t0.8*[ORGPRC]),0,ISALES]) AS FULLPRICE" myquery myquery & " FROM ((CRLMSALES LEFT JOIN ORGPRICE ON CRLMSALES.ST
Y015 =-"
myquery = myquery & "ORGPRICE.STY622) LEFT JOIN TBL SALESMAN NAME ON " myquery myquery & "CRLMSALES.SLM015 = TBL SALESMAK NAME.SLRNO) LEFT JOI
N Division "
myquery = myquery & "ON CRLMSALES.-DIV015 = Division.Division "
myquery myquery & " GROUP BY CRLMSALES.SLM015, CRLMSALES.DIV015, CRLMSA
LES.NAME00, "
myquery = myquery & "CRLMSALES.STY015, CRLMSALES.OVP015, TBL SALESMAN NAM E.SLNAME, "
myquery = myquery & "ORGPRICE.LastOfSTP622, CRLMSALES.ACT015, CRLMSALES.N AM201, "
myquery = myquery & "ORGPRICE.LastOfSTP622, [emo015] & ' - ' & [eyr015],
myquery = myquery 4 "Division.Div_Name"
myquery = myquery & " ORDER BY CRLMSALES.SLM015, CRLMSALES..STY015;"
DBS.Execute (myquery)
In above query, I see: "ON CRLMSALES.-DIV015" .  That doesn't look right. Neither does "iif([0VP015]<t0.8". Neither does "N AM201" and "CRLMSALES..STY015" and "TBL SALESMAN NAM E.SLNAME". TEL_ SALESMAN NAME needs to be bracketed if it contains a space.

Another one: "myquery myquery" is missing an = sign (in more than one place).
------------------------------------------
General comments - It's pretty evident you are a victim of sloppy coding here.  I've found numerous typos and undoubtedly there are more.  Frankly, it's sometimes difficult to build decent queries with all that string concatenation stuff.  So, I have some words of advice.
First, get the qet the SQL working independently before you even attempt to incorporate it into your VB. Access has a wonderful query designer that lets you build and test these kind of queries with ease. When you are all done you can just paste the working SQL into your program.
Second, if you have a query working independently, you can store it in a query object. Then, your VB gets much simpler because you can just run the querydef (One or two
 
Third, put in some error handling so you can better identify where the error is coming from. This will not be the last time you have to track down an error like this.



 








 





0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

730 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