Access database runtime error 3061: too few paremeters. Expected2

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
vidiagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
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
vidiagAuthor Commented:
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
dqmqCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.