Solved

Access database runtime error 3061: too few paremeters. Expected2

Posted on 2007-03-28
3
270 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
  • 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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now