• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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

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
JAilBreak
Asked:
JAilBreak
  • 19
  • 14
  • 4
  • +1
1 Solution
 
hongjunCommented:
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
 
JAilBreakAuthor Commented:
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
 
JAilBreakAuthor Commented:
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
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!

 
hongjunCommented:
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
 
JAilBreakAuthor Commented:
Sorry give the same error
Too few parameters. Expected 1

Why this dont work? :( :(
0
 
hongjunCommented:
Do this before this sql statement.

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


hongjun
0
 
hongjunCommented:
Just to see what is the contents. Post it here.

hongjun
0
 
JAilBreakAuthor Commented:
I have done something similar

And values are

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

is this what u mean?
0
 
PaulHewsCommented:
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
 
CyrexCore2kCommented:
You wouldn't be having so much of a problem if u used ado instead of dao... you might consider switching.

Spencer Ruport
0
 
PaulHewsCommented:
>>You wouldn't be having so much of a problem if u used ado instead of dao<<

Bull.
0
 
JAilBreakAuthor Commented:
Tks for your support Paul.
But can u explain more about that debug??
I did not understand correctly.
0
 
PaulHewsCommented:
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
 
JAilBreakAuthor Commented:
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
 
PaulHewsCommented:
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
 
JAilBreakAuthor Commented:
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
 
JAilBreakAuthor Commented:
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
 
PaulHewsCommented:
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
 
JAilBreakAuthor Commented:
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
 
PaulHewsCommented:
>>>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
 
PaulHewsCommented:
>>>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
 
JAilBreakAuthor Commented:
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
 
JAilBreakAuthor Commented:
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
 
PaulHewsCommented:
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
 
PaulHewsCommented:
Ignore that. I see what you are doing.

How is MestreMes field defined?
0
 
PaulHewsCommented:
>How is MestreMes field defined? < 

I mean how is MestreMes.parque_trim defined.
0
 
JAilBreakAuthor Commented:
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
 
PaulHewsCommented:
Ok.  I don't understand why you are replacing , with . but I guess you have your reason.  Does your update work then?
0
 
JAilBreakAuthor Commented:
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
 
JAilBreakAuthor Commented:
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
 
JAilBreakAuthor Commented:
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
 
PaulHewsCommented:
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
 
JAilBreakAuthor Commented:
:(

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

:( any ideas??

sorry :(
0
 
JAilBreakAuthor Commented:
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
 
JAilBreakAuthor Commented:
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
 
PaulHewsCommented:
It's late here.  I'll check it first thing tomorrow
0
 
JAilBreakAuthor Commented:
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
 
PaulHewsCommented:
Just happy you found a resolution to your problem. :) Thanks for the points.
0

Featured Post

Industry Leaders: 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!

  • 19
  • 14
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now