Too few Parameters error in Function

[psperry2]

This Function gets an error on the first set rst= line. it is called from a query. I don't see anything requiring parameters here.

Function Completd(PersonID As Variant) As Variant
Dim Numb_Completed As Integer
Dim Total_Steps As Integer
Dim Pct_Completed As Variant
Dim mydb As Database
Dim rst As Recordset

Set mydb = CurrentDb

Set rst = mydb.OpenRecordset("Select count(*) as Numb_Completed FROM tblPersons_Steps WHERE TblPersons_Steps.InvID = " & PersonID & " AND TblPersons_Steps.Status IN ('N/A', 'Passed', 'Received', 'Dropped')")
Numb_Completed = rst!Numb_Completed

Set rst = mydb.OpenRecordset("Select count(*) as Total_Steps FROM tblPersons_Steps WHERE TblPersons_Steps.ISInvID =" & PersonID)
Total_Steps = rst!Total_Steps

If Total_Steps > 0 Then
Pct_Completed = (Numb_Completed / Total_Steps) * 100
Else
Pct_Completed = 0
End If

Completd = Pct_Completed
End Function

 

[genomon]

Function Completd(PersonID As Variant) As Variant

Are you sure you are passing PersonID with the function call? It's not optional.

"Time flies like an arrow; fruit flies like a banana."

 

[Golom]

Try qualifying your objects

Dim mydb As [red]DAO.[/red]Database
Dim rst  As [red]DAO.[/red]Recordset

If you have both DAO and ADO references then it will pick up the first one in the list of references.

 

[psperry2]

Golom I am unsure what to do, Am I supposed to change the Function to put in DAO string in the DIM stmts?

I don't know the difference between ADO and DAO.

Genomon the argument is passed from the query:

SELECT 0 AS StepSort, InvFName, InvLName, " " AS StepDateStart1, Status, 000.00 AS Pct_Completed
FROM tblPersons
INNER JOIN tblPersons_Steps ON tblPersons.InvID = tblPersons_Steps.ISInvID
WHERE tblPersons_Steps.Stepsort<>99 AND tblPersons.status = "ACTIVE"
UNION SELECT tblPersons_Steps.StepSort, tblPersons.InvFName, tblPersons.InvLName,
Format([StepDateStart],"yyyy/mm/dd") AS StepDateStart1,
tblPersons.Status, Completd(tblPersons.InvID) AS Pct_Completed
FROM tblPersons
INNER JOIN tblPersons_Steps ON tblPersons.InvID = tblPersons_Steps.ISInvID
WHERE tblPersons_Steps.StepSort=99 AND tblPersons.status = "ACTIVE";

 

[genomon]

Am I supposed to change the Function to put in DAO string in the DIM stmts?

Do that, or go to the VBA IDE, choose Tools|References, highlight the DAO reference, and use the up arrow on the side of the form to move it above the ADO reference, giving it precedence.


"Time flies like an arrow; fruit flies like a banana."

 

[psperry2]

I did that and still get the same error. Too Few parameters.

Here is the new code:

Function Completd(PersonID As Variant) As Variant
Dim Numb_Completed As Integer
Dim Total_Steps As Integer
Dim Pct_Completed As Variant
Dim mydb As DAO.Database
Dim rst As DAO.Recordset

Set mydb = CurrentDb

Set rst = mydb.OpenRecordset("Select count(*) as Numb_Completed FROM tblPersons_Steps WHERE TblPersons_Steps.InvID = " & PersonID & " AND TblPersons_Steps.Status IN ('N/A', 'Passed', 'Received', 'Dropped') ")
Numb_Completed = rst!Numb_Completed

Set rst = mydb.OpenRecordset("Select count(*) as Total_Steps FROM tblPersons_Steps WHERE TblPersons_Steps.ISInvID =" & PersonID)
Total_Steps = rst!Total_Steps

If Total_Steps > 0 Then
Pct_Completed = (Numb_Completed / Total_Steps) * 100
Else
Pct_Completed = 0
End If

Completd = Pct_Completed
End Function

 

[psperry2]

I commented out the first rst = and tried the function without that. Got no errors. The only difference between the first rst= stmt and the second is the second condition:

AND TblPersons_Steps.Status IN ('N/A', 'Passed', 'Received', 'Dropped')

So the problem must be in this part

 

[Golom]

Paste and run this into the SQL view of a new query

Select DISTINCT Status From TblPersons_Steps

Does it run without errors?

 

[psperry2]

There is no error, but I get prompted to type in a status.

 

[PWise]

try
Set rst = mydb.OpenRecordset("Select count(*) as Numb_Completed FROM tblPersons_Steps WHERE TblPersons_Steps.Status IN ('N/A', 'Passed', 'Received', 'Dropped') and TblPersons_Steps.InvID = " & PersonID )


or
dim sqltxt
Sqltxt = Select count(*) as Numb_Completed FROM tblPersons_Steps WHERE TblPersons_Steps.InvID = " & PersonID & " AND TblPersons_Steps.Status IN ('N/A', 'Passed', 'Received', 'Dropped') "
debug.print Sqltxt
Set rst = mydb.OpenRecordset(sqltxt)

what prints in the debugw indo

 

[Golom]

Then "Status" IS NOT a field in "TblPersons_Steps"!!

You need to check the field definitions in that table.

Note that a Field can have a "Caption" property assigned and that is what will appear when you open the table. You must look at the design view of the table to see the real field name.

 

[psperry2]

I tried both ways and still get the "Too few paramters error" and it indicates that I need 2 more paramters.

In the watch window sqltxt = "Select count(*) as Numb_Completed FROM tblPersons_Steps WHERE TblPersons_Steps.InvID = 234 AND TblPersons_Steps.Status IN ('N/A', 'Passed', 'Received', 'Dropped')

 

[psperry2]

Golom Status is a text field in the tblPersons_Steps. I did check.

 

[psperry2]

Sorry guys, the actual field name is stepstatus not status.

 

[psperry2]

So after correcting that error I still get Too few Parameters. But it is only looking for one this time. So I will look for other miss-spelled field names.

 

[Golom]

Now you can go back to the queries that pwise and I gave you in the other thread and, with that change, they should give you what you want in a single query.

 

[psperry2]

Is there a way to view the recordset in a browse window?

I am getting zeros calculated every time for the perCent complete and I know that some are 100% complete.

 

[Golom]

Which recordset?

Are you using the query from the other thread or are you using the Function from this thread?

 

[psperry2]

the function from this thread

 

[Golom]

Function Completd(PersonID As Variant) As Variant
    Dim Numb_Completed              As Integer
    Dim Total_Steps                 As Integer
    Dim Pct_Completed               As Variant
    Dim mydb                        As DAO.Database
    Dim rst                         As DAO.Recordset
    Dim SQL1                        As String
    Dim SQL2                        As String

    Set mydb = CurrentDb
    SQL1 = "Select Count(*) as Numb_Completed " & _
           "FROM tblPersons_Steps " & _
           "WHERE TblPersons_Steps.InvID = " & PersonID & _
           "  AND TblPersons_Steps.StepStatus IN ('N/A', 'Passed', 'Received', 'Dropped') "
    
    Set rst = mydb.OpenRecordset(SQL1)
    Numb_Completed = rst!Numb_Completed

    SQL2 = "Select count(*) as Total_Steps " & _
           "FROM tblPersons_Steps " & _
           "WHERE TblPersons_Steps.ISInvID =" & PersonID
    Debug.Print SQL2
    Set rst = mydb.OpenRecordset(SQL2)
    Total_Steps = rst!Total_Steps
    
    [COLOR=black cyan] 'Debug.Print IS YOUR FRIEND![/color]
    [COLOR=black cyan] ' Copy and paste these SQL strings from the immediate window to[/color]
    [COLOR=black cyan] ' the SQL view of a query and run them.[/color]
    Debug.Print "PersonID = " & PersonID, "Number Completed = " & Numb_Completed, "Total Steps = " & Total_Steps
    Debug.Print SQL1
    Debug.Print SQL2

    If Total_Steps > 0 Then
        Pct_Completed = (Numb_Completed / Total_Steps) * 100
    Else
        Pct_Completed = 0
    End If

    Completd = Pct_Completed
End Function