?
Solved

Errror Too few parameters. Expected 1 -  Why???

Posted on 2003-03-20
38
Medium Priority
?
166 Views
Last Modified: 2010-05-01
Hi!

I have a Sql query in my VB, to update a table but in the Access97 the query runs fine, but in my VB give me this error. Why?

Many Tanks.

This is the sSql

Select Case Val(cmbMes.Text)
   Case 1, 2, 3
     sMesInicio = "01": sMesFim = Format(Val(cmbMes.Text), "00")
     Trimestre = 1
           
   Case 4, 5, 6
     sMesInicio = "04": sMesFim = Format(Val(cmbMes.Text), "00")
     Trimestre = 4
       
   Case 7, 8, 9
     sMesInicio = "07": sMesFim = Format(Val(cmbMes.Text), "00")
     Trimestre = 7
       
     Case 10, 11, 12
     sMesInicio = "10": sMesFim = Format(Val(cmbMes.Text), "00")
     Trimestre = 10
End Select

sSql = "INSERT INTO _ComprasQuadrimestre ( Numsicor, VNet, VCat ) " & _
"SELECT Agentes.Numsicor, Sum(([MS_NET]+[DO_NET]+[VA_NET])) AS VNet, Sum(([MS_Cat]+[DO_Cat]+[VA_Cat])) AS VCat " & _
"FROM Agentes INNER JOIN Compras_Acumuladas ON Agentes.Numpecas = Compras_Acumuladas.NUMPECAS " & _
"Where (((Compras_Acumuladas.ANO) = '" & cmbAno.Text & "') And ((Compras_Acumuladas.MES) >= " & Val(sMesInicio) & ") AND ((Compras_Acumuladas.MES)<= " & Val(sMesFim) & ")) " & _
"GROUP BY Agentes.Numsicor; "
»»»»»»»»»»»»»»» This INSERT INTO works fine »»»»»»»»»»»»»»»»»»»»»»»»»

rstTemporario.Close
Set rstTemporario = daoDatabase.OpenRecordset("SELECT Sum (Parque_Veiculo.Veiculos) AS trimestre FROM [Parque_Veiculo]" & _
"Where (((parque_veiculo.ANO) = '" & Val(cmbAno.Text) & "') AND ((parque.veiculo.MES) >= '" & Val(sMesInicio) & "') AND ((parque.veiculo.MES)<= '" & Val(sMesFim) & "')) " & _
"GROUP BY (Parque_Veiculo.Numpecas);"

»»»»»»»»» This SELECT give the error - Before UPDATE »»»»»»»»»»»»»»»

sSql = "UPDATE MestreMes SET [parque_trim] = " & Replace(rstTemporario.Fields("parque_trim"), ",", ".")
daoDatabase.Execute (sSql)

»»»»»»» And i tried whith this Between but give the same error »»»»»»»»»»»»
'"Where (((parque_veiculo.ANO) = '" & Val(cmbAno.Text) & "') AND ((parque.veiculo.MES) between '" & Val(Trimestre) & "' and '" & Val(cmbMes.Text) & "'))" & _

»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»

WHERE IS THE ERROR??? :(





0
Comment
Question by:JAilBreak
[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
  • 19
  • 14
  • 4
  • +1
38 Comments
 
LVL 33

Expert Comment

by:hongjun
ID: 8174043
Try this
Set rstTemporario = daoDatabase.OpenRecordset("SELECT Sum (Parque_Veiculo.Veiculos) AS trimestre FROM [Parque_Veiculo] " & _
"Where (((parque_veiculo.ANO) = '" & Val(cmbAno.Text) & "') AND ((parque.veiculo.MES) >= '" & Val(sMesInicio) & "') AND ((parque.veiculo.MES) <= '" & Val(sMesFim) & "')) " & _
"GROUP BY (Parque_Veiculo.Numpecas))"


hongjun
0
 

Author Comment

by:JAilBreak
ID: 8174104
Tks hongjun

But first give me "Expected: list separator or )"
tehn i put a ) in the end and give me "Extra)in query expression '(parque.veiculo.numpecas))'

?????? Any ideas
0
 

Author Comment

by:JAilBreak
ID: 8174124
Tks hongjun

But first give me "Expected: list separator or )"
tehn i put a ) in the end and give me "Extra)in query expression '(parque.veiculo.numpecas))'

?????? Any ideas
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:hongjun
ID: 8174178
Try this again.
Set rstTemporario = daoDatabase.OpenRecordset("SELECT Sum (Parque_Veiculo.Veiculos) AS trimestre FROM [Parque_Veiculo] " & _
"Where (((parque_veiculo.ANO) = '" & Val(cmbAno.Text) & "') AND ((parque.veiculo.MES) >= '" & Val(sMesInicio) & "') AND ((parque.veiculo.MES) <= '" & Val(sMesFim) & "')) " & _
"GROUP BY (Parque_Veiculo.Numpecas)")


hongjun
0
 

Author Comment

by:JAilBreak
ID: 8174282
Sorry give the same error
Too few parameters. Expected 1

Why this dont work? :( :(
0
 
LVL 33

Expert Comment

by:hongjun
ID: 8174438
Do this before this sql statement.

Debug.Print Val(cmbAno.Text)
Debug.Print Val(sMesInicio)
Debug.Print Val(sMesFim)


hongjun
0
 
LVL 33

Expert Comment

by:hongjun
ID: 8174442
Just to see what is the contents. Post it here.

hongjun
0
 

Author Comment

by:JAilBreak
ID: 8174546
I have done something similar

And values are

(cmbAno.Text)= "2003"
(sMesInicio)="1"
(sMesFim)="2"

is this what u mean?
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 8175972
I would do this:
debug.print sSql

after the query string is built and copy and paste the result from the debug window into a new query (create new query, select SQL mode and paste) in Access to see if it runs.  If it doesn't, it may highlight the error as Access does a little better in helping debug SQL statements than VB.
0
 
LVL 14

Expert Comment

by:CyrexCore2k
ID: 8176060
You wouldn't be having so much of a problem if u used ado instead of dao... you might consider switching.

Spencer Ruport
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 8176350
>>You wouldn't be having so much of a problem if u used ado instead of dao<<

Bull.
0
 

Author Comment

by:JAilBreak
ID: 8177132
Tks for your support Paul.
But can u explain more about that debug??
I did not understand correctly.
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 8177202
Your code would look like this:
----------
sSql = "INSERT INTO _ComprasQuadrimestre ( Numsicor, VNet, VCat ) " & _
"SELECT Agentes.Numsicor, Sum(([MS_NET]+[DO_NET]+[VA_NET])) AS VNet, Sum(([MS_Cat]+[DO_Cat]+[VA_Cat])) AS VCat " & _
"FROM Agentes INNER JOIN Compras_Acumuladas ON Agentes.Numpecas = Compras_Acumuladas.NUMPECAS " & _
"Where (((Compras_Acumuladas.ANO) = '" & cmbAno.Text & "') And ((Compras_Acumuladas.MES) >= " & Val(sMesInicio) & ") AND ((Compras_Acumuladas.MES)<= " & Val(sMesFim) & ")) " & _
"GROUP BY Agentes.Numsicor; "

Debug.Print sSql

rstTemporario.Close

----------

Now put a breakpoint on rstTemporario.Close.  Run the program.  When it hits the breakpoint, open the immediate window and select and copy the print of sSql to the clipboard.  Shut down your program.

Open the database in Access.  Click 'Queries' tab.  Click the new button.  Enter design mode.  Close the 'Show Table' dialog.  Click the SQL mode button.  Now paste the text you copied from the immediate window.  If Access has any problems with your query, it should highlight the problem when you try to execute it here.


0
 

Author Comment

by:JAilBreak
ID: 8177253
Ok Paul but the problem is in the Update query
The INSERT INTO works fine
Must i put Debug.Print sSql after

rstTemporario.Close
Set rstTemporario = daoDatabase.OpenRecordset("SELECT Sum (Parque_Veiculo.Veiculos) AS trimestre FROM [Parque_Veiculo]" & _
"Where (((parque_veiculo.ANO) = '" & Val(cmbAno.Text) & "') AND ((parque.veiculo.MES) >= '" & Val(sMesInicio) & "') AND ((parque.veiculo.MES)<= '" & Val(sMesFim) & "')) " & _
"GROUP BY (Parque_Veiculo.Numpecas);"

Debug.Print sSql

is this ok?



0
 
LVL 38

Expert Comment

by:PaulHews
ID: 8177279
Not really... Do it like this:

sSql = "SELECT Sum (Parque_Veiculo.Veiculos) AS trimestre FROM [Parque_Veiculo]" & _
"Where (((parque_veiculo.ANO) = '" & Val(cmbAno.Text) & "') AND ((parque.veiculo.MES) >= '" & Val(sMesInicio) & "') AND ((parque.veiculo.MES)<= '" & Val(sMesFim) & "')) " & _
"GROUP BY (Parque_Veiculo.Numpecas);"

Debug.Print sSql
rstTemporario.Close

Set rstTemporario = daoDatabase.OpenRecordset(sSql)
0
 

Author Comment

by:JAilBreak
ID: 8177353
ok i will try

but u must use the UPDATE query to update de table

i have after the set select, this

sSql = "UPDATE MestreMes SET [parque_trim] = " & Replace(rstTemporario.Fields("parque_trim"), ",", ".")
daoDatabase.Execute (sSql)

to use the select to update the MestreMes table every 3 month

0
 

Author Comment

by:JAilBreak
ID: 8177466
Ok now i see something

in the immediate show this

"SELECT SUm[Parque_veiculo.Veiculos] AS trimestre FROM [Parque_Veiculo] Where (((Parque_Veiculo.ANO) = '2003')
AND ((PArque_Veiculo.MES) >= '1' AND ((PArque_Veiculo.MES) <= '2')) GROUP BY (Parque_Veiculo.Numpecas);

Give this error
Error "Data type mismatch in criteria expression"

on help say this
"The criteria expression in FIND method is attempting to compare a field whith a value whose data type does not match the field´s data type"

One question.
Why give this error on field type on this SELECT but dind not give in the INSERT INTO, where the fields (sMesInicio) and (sMesFim) are the same??
Any idea??

I am getting there, it’s so close :)
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 8177495
Are the fields ANO and MES text fields?  If not, get rid of the quotation marks:

SELECT Sum[Parque_veiculo.Veiculos] AS trimestre FROM [Parque_Veiculo] Where (((Parque_Veiculo.ANO) = 2003)
AND ((PArque_Veiculo.MES) >= 1 AND ((PArque_Veiculo.MES) <= 2)) GROUP BY (Parque_Veiculo.Numpecas);
0
 

Author Comment

by:JAilBreak
ID: 8177500
by the way

the fields from the table Parque_Veiculo ANO and MES are
ANO = Text - Filed Size 4
MES = Number - Integer

and the fields from sMesInicio and sMesFim are

Dim sMEsInicio as String
Dim SMesFim as String

Is this the problem, this type of fields?? If so why in the INSERT INTO result?

Dont understand

:(
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 8177512
>>>One question.
Why give this error on field type on this SELECT but dind not give in the INSERT INTO, where the fields (sMesInicio) and (sMesFim) are the same??
Any idea??<<<

The INSERT query is comparing Compras_Acumuladas.ANO which may be a text field.  Perhaps Parque_Veiculo.ANO is not??

0
 
LVL 38

Expert Comment

by:PaulHews
ID: 8177521
>>>the fields from the table Parque_Veiculo ANO and MES are
ANO = Text - Filed Size 4
MES = Number - Integer<<<

Bingo!  So keep the quotes for ANO, but lose them for MES:

sSql = "SELECT Sum (Parque_Veiculo.Veiculos) AS trimestre FROM [Parque_Veiculo]" & _
"Where (((parque_veiculo.ANO) = '" & Val(cmbAno.Text) & "') AND ((parque.veiculo.MES) >= " & Val(sMesInicio) & ") AND ((parque.veiculo.MES)<= " & Val(sMesFim) & ")) " & _
"GROUP BY (Parque_Veiculo.Numpecas);"


0
 

Author Comment

by:JAilBreak
ID: 8177578
Yep it’s works on the access with no errors
Is so easy that make a guy to cry

now Paul i must update the table MestreMes
will i use the update next os this??

If so, can u give me an example

And many tanks for your support
0
 

Author Comment

by:JAilBreak
ID: 8177610
i will change the select because i need some information from another table

this is the example

"SELECT DISTINCT agentes.numsicor, Sum(PArque_Veiculos) AS PArque_Trim
FROM Parque_Veiculo  INNER JOIN agentes ON Parque_Veiculo.Numpecas = agentes.Numpecas
Where (((parque_veiculo.ANO) = '" & Val(cmbAno.Text) & "') AND ((parque.veiculo.MES) >= " & Val(sMesInicio) & ") AND ((parque.veiculo.MES)<= " & Val(sMesFim) & ")) " & _
"GROUP BY (agentes.Numsicor);"
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 8177621
I don't really understand what you are doing here:

sSql = "UPDATE MestreMes SET [parque_trim] = " & Replace(rstTemporario.Fields("parque_trim"), ",", ".")
daoDatabase.Execute (sSql)


parque_trim is not a field that we selected in the previous select, so it is not present in the recordset.
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 8177641
Ignore that. I see what you are doing.

How is MestreMes field defined?
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 8177649
>How is MestreMes field defined? < 

I mean how is MestreMes.parque_trim defined.
0
 

Author Comment

by:JAilBreak
ID: 8177706
is defined as a Number - Double?? I this correct to run this?

the value in this field is between "1" and "10000" this is the SUM of the selling of Veiculo of 3 month Group by Numsicor=Numpecas, this is the number of the Agent that sell the Veiculo "that means Car"
This wills calculate the sum of the car selling between 3 month. This update must run every 3 moth

Understand?
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 8177761
Ok.  I don't understand why you are replacing , with . but I guess you have your reason.  Does your update work then?
0
 

Author Comment

by:JAilBreak
ID: 8177785
but now after i use this SELECT

sSQL = "SELECT DISTINCT agentes.numsicor, Sum(PArque_Veiculos) AS Trimestre FROM [Parque_Veiculo]  INNER JOIN [agentes] ON (Parque_Veiculo.Numpecas = agentes.Numpecas)" & _
Where (((parque_veiculo.ANO) = '" & Val(cmbAno.Text) & "') AND ((parque.veiculo.MES) >= " & Val(sMesInicio) & ") AND ((parque.veiculo.MES)<= " & Val(sMesFim) & ")) " & _
"GROUP BY (agentes.Numsicor);"

Error "Join expression not supported"

But in query that i mayde in the Access works fine

"SELECT DISTINCT agentes.Numsicor, SUM(Parque_Veiculo.Veiculos) AS Trimestre
FROM Parque_veiculo INNER JOIN agentes ON Parque_veiculo.Numpecas = agentes.Numpecas
Where (((PAruqe_Veiculo)="2003" AND (PArque_Veiculo.MES) Between 4 AND 7
GROUP BY agentes.Numsicor;"

This query works on access, but after i try to use in the VB give that JOIN error.
Can u help on this last thing, for i close this Question and give u the points, is few but it´s all i have :)

Tks again Paul
0
 

Author Comment

by:JAilBreak
ID: 8177837
This is big a confusion Paul
This is a program that imports 4 text files from a UNIX system.
Then the program read the files and line by line import the values into some tem tables that will generate a big table Call MestreMes and the table Parque_Veiculo is a temp
That has some values from the selling car from the selling agent and I will read from this table the last 3 moth and updating the field Parque_trim from the MestreMes, but in the same record in the MestreMes table are lots of field  whit dates and selling values % par agent, and for this SELECT I just want to update this field not the rest

Tank you for your Patience for me
0
 

Author Comment

by:JAilBreak
ID: 8178138
PAUL PLEASE THIS FINAL QUESTION!

Everything is working fine
No errors
But the update give me values that is not real
This is the final SELECT and works


rstTemporario.Close
Set rstTemporario = daoDatabase.OpenRecordset("SELECT DISTINCT Sum (Parque_Veiculo.Veiculos) AS TotalTrim FROM INNER JOIN [agentes] ON (Parque_Veiculo.Numpecas = agentes.Numpecas) " & _
"WHERE ((PArque.Veiculo.ANO) = '" & Val(cmbAno.Txt) & "' AND ((PArque_Veiculo.MES) => " & Val(sMesInicio) & ") AND ((parque_Veiculo.MES) <= " & Val(sMesFim) & ")) " & _
"Group BY (agentes.Numsicor);")

sSql = "UPDATE MestreMes SET parque_trim = " & Replace (rstTemporario.fields(TotalTrim), ",", ".")
daoDatabase.Execute (sSql)

This is the all UPDATE
But the TotalTrim Field or parque_trim is all the time = 2 why?? the 2 is the value of the (sMesFim)

Paul just this final question PLEASE
I must finish this tomorrow
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 8178173
A couple of points.  The update query will update ALL RECORDS in the table changing the value of parque_trim.  I hope this is what you want...

Also, the totaltrim field is numeric, so you should not be using Replace function on it.

rstTemporario.Close
Set rstTemporario = daoDatabase.OpenRecordset("SELECT DISTINCT Sum (Parque_Veiculo.Veiculos) AS TotalTrim FROM INNER JOIN [agentes] ON (Parque_Veiculo.Numpecas = agentes.Numpecas) " & _
"WHERE ((PArque.Veiculo.ANO) = '" & Val(cmbAno.Txt) & "' AND ((PArque_Veiculo.MES) => " & Val(sMesInicio) & ") AND ((parque_Veiculo.MES) <= " & Val(sMesFim) & ")) " & _
"Group BY (agentes.Numsicor);")

Debug.Print rstTemporario("TotalTrim")

sSql = "UPDATE MestreMes SET parque_trim = " & CStr(rstTemporario("TotalTrim"))
daoDatabase.Execute (sSql)

The Debug.Print is just so that you can make sure you are getting what you want from the query.  
0
 

Author Comment

by:JAilBreak
ID: 8178240
:(

error 3075 "Extra in query expression '((parque_veiculo.ANO)='2003') AND ((Parque_Veiculo.MES) >= 1) AND ((Parque_Veiculo.MES) <=2))'

:( any ideas??

sorry :(
0
 

Author Comment

by:JAilBreak
ID: 8178293
No more that error i fix it

But the value for the parque_trim is not ok

put in all the records the same value 2
But this is not this that i want
I want the sum of the month for the parque_veiculo.Veiculos
a deferent value for each record
in the Access97 this select make the sum ok for each record/numsicor


:(

there will be an end to this?? :(
0
 

Author Comment

by:JAilBreak
ID: 8178335
Can u tell me anything PAUL because i must go to sleep
Tomorrow i must work early in the morning
And i must have this work
0
 
LVL 38

Accepted Solution

by:
PaulHews earned 260 total points
ID: 8178534
It's late here.  I'll check it first thing tomorrow
0
 

Author Comment

by:JAilBreak
ID: 8178560
Ok tks Paul but i have done it
I made some change and it ok
It works

Tanks again for help and for your Patience for me

See u again next time

If u want to tell u how i made
Just ask me and i will tell u tomorrow

But now i must Sleep

Many tks Paul
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 8180732
Just happy you found a resolution to your problem. :) Thanks for the points.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

777 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