DAO.QueryDef error message (Too few parameters. Expected 3. Error 3061)

Question

Hello, I am not a programmer can you assist me with this please! I am using below codes 2 different way and they all giving me this error message (Too few parameters. Expected 3. Error 3061)

Below is my "rollup query" (qryPercentage). Created a filedcalled  "Convert" to get real number for calculation instead of using "ProdPercentage" filed since that filed has percentage "%".

qryPercentage

Productive Hours	Earned Hours	Total	ProdPercentage	Convert	UserID
1,169.93	1,654.59	15,458.00	141.43%	141.43	D844

Dim Db              As DAO.Database  'current database
Dim RS              As DAO.Recordset 'holds query result
Dim qdf             As QueryDef
Dim sPercentage     As String

Set Db = CurrentDb

'run query Defs
Set qdf = Db.QueryDefs("qryPercentage")
Set RS = qdf.OpenRecordset
sPercentage = RS.Fields("Convert").Value

****** Second way of coding ******

Dim Db              As DAO.Database  'current database
Dim RS              As DAO.Recordset 'holds query result
Dim sQuery          As String
Dim sPercentage     As String

Set Db = CurrentDb
sQuery = "SELECT qryPercentage.Convert FROM qryPercentage;"
Set RS = Db.OpenRecordset(sQuery)
sPercentage = RS.Fields("Convert").Value

Thank you very much!

---

JayZ

Answer 1

Could you please open qryPercentage in SQL View, copy the SQL text and paste it into a reply here? Thanks in advance.

---

Regards, Hans Vogelaar (https://www.eileenslounge.com)

Answer 2

sQuery = "SELECT qryPercentage.Convert FROM qryPercentage;"

Hi JayZ,

I bet you have no field Convert in qryPercentage.

Imb.

Answer 3

Thank you for quick respond! When I run the query by itself the data are there but when I run the code is not reading any fields. :(

SELECT qryTotalFilterSecond.SumOfDirectHours AS [Productive Hours], qryTotalFilterSecond.SumOfSumofEarned AS [Earned Hours], qryTotalFilterSecond.SumOfSumofVolume AS [Total Unit], ([SumOfSumofEarned]/[SumOfDirectHours]) AS ProductionPercentage, [ProductionPercentage]*100 AS [Convert], qryTotalFilterSecond.UserID
FROM qryTotalFilterSecond;

Thanks

---

JayZ

Answer 4

Thanks. I'd also need to see the SQL of qryTotalFilterSecond, and all queries that qryTotalFilterSecond is based on (if any).

---

Regards, Hans Vogelaar (https://www.eileenslounge.com)

Answer 5

See https://www.devhut.net/2011/11/07/ms-access-vba-run-parameter-query-in-vba/

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 6

Hello, tried this code as you link suggested but the code reads (txt fields on the open Form) instead of query "qryPercentage". What I am doing wrong?

    Dim db                    As DAO.Database
    Dim qdf                   As DAO.QueryDef
    Dim prm                  As DAO.Parameter
    Dim rs                     As DAO.Recordset
    Dim sPercentage       As String
   
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryPercentage")
 
    For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
    Next prm
 
    Set rs = qdf.OpenRecordset
 
    'Work with the recordset
    sPercentage = rs.Fields("Convert").Value
   
    rs.Close 'Close the recordset
    'Cleanup after ourselves
    Set db = Nothing
    Set qdf = Nothing

 

---

JayZ

Answer 7

not sure your goal; just thought I would add - -

if your query runs ok when you manually run it from the Navigation pane...

then in code you can use: docmd.OpenQuery "YourQueryName"

....not sure if this is what you seek though

Answer 8

Your code looks fine at first sight.  Add a temporary debug line as follows:

    For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
        Debug.Print prm.Name
    Next prm

When the code is executed the names of all parameters referenced by the query or any queries it references, directly or indirectly, will be listed to the immediate window (press Ctrl+G to open it).  These should be names of objects which are currently exposed, e.g. controls on an open form.  Is there anything else listed, which you would not expect to be, e.g. a control on a form not currently open?

Note that a simple parameter such as [Enter start date:] cannot be used in a query, or any query it references when opening a recordset in code.  However, that would raise a different error, as such a parameter cannot be evaluated, so I don't think it is the problem in your case.

I'm not clear as to what you mean by 'but the code reads (txt fields on the open Form) instead of query "qryPercentage"'?  At first glance it would suggest that the Convert column in the result table is the value of a parameter.

---

Ken Sheridan, Stafford, England

Answer 9

PS:  what happens if you amend the query as follows?

………[Total Unit], ([SumOfSumofEarned]/[SumOfDirectHours]) AS ProductionPercentage, ([SumOfSumofEarned]/[SumOfDirectHours])*100 AS [Convert],…………

---

Ken Sheridan, Stafford, England