Run-time error 3061 1

[FoxProProgrammer]

Hi folks,

I am helping a colleague with his database. He designed the database, tables, queries, etc, and I am just trying to help him implement a Median function within his structure. I wrote the Median function (code below), and tested it with a small table that I created. It works; however, when I use the function in a query in his database I get run-time error 3061: Too few parameters. Expected 2.

Public Function Median(fldname As String, tblname As String) As Single

Dim strQuery As String
Dim db As DAO.Database
Dim rs2 As DAO.Recordset
Dim numRecs, dataPt As Integer

' Get the data and sort it.
strQuery = "Select [" & fldname & "] From [" & tblname & "] Where NOT IsNull([" & fldname & "]) Order by [" & fldname & "];"
Set db = CurrentDb
Set rs2 = db.OpenRecordset(strQuery, dbOpenDynaset, dbReadOnly)

' Count the number of records and move to the beginning of record set.
rs2.MoveLast
numRecs = rs.RecordCount
rs2.MoveFirst

dataPt = Int((numRecs + 1) / 2)

' Find the Median
If dataPt Mod 2 = 0 Then
    ' Even number of records.  Average the middle two values.
    rs2.Move dataPt - 1
    Median = rs.Fields(fldname)
    rs2.MoveNext
    Median = (Median + rs2.Fields(fldname)) / 2
Else
    ' Odd number of records.
    rs2.Move dataPt - 1
    Median = rs2.Fields(fldname)
End If

End Function

Here is a summary of the structure of his database:

TableA - Resides on a network drive.

QueryA - Retrieves data from TableA. One field in QueryA is a calculated field, derived as follows:

Select TableA.fld1 - TableA.fld2 As calcfld1 ... Between Forms!Form1!fld3 And Forms!Form1!fld4

Note that fld3 and fld4 create a parameter query. Therein lies the problem, I think, which is why I created a form to enter that data. Before I created a form, Access would ask the user to enter the data, and I thought that I could fix the problem by entering the data on a form. No such luck.

QueryB - Retrieves data from QueryA and calculates various aggregate functions, one of which is my Median function:

Select Median("calcfld1", "QueryA") As MedianCalcfld ...

I have done the following to try to resolve this problem:

* Checked the spelling of "fldname" and "tblname" in the Median function call to make sure that they are correct.

* Ran the debugger to try to look at the record set, which I can't do because the program bombs when it gets to the statement where it creates the record set. (Set rs = ...)

* Copied the query string (strQuery) created in the Median function to the query builder and ran the query. It worked there, so the SQL is good.

* Looked in QueryA to make sure that fldname exists. It does.

Ok, I thought that maybe the two parameters that it was looking for are fld3 and fld4, which are the two parameters required to run QueryA. In an attempt to resolve this, I created a form that contains two text boxes and a command button. One text box is named fld3 and the other is named fld4. When the command button is pressed, the OnClick procedure runs QueryB as follows:

Private Sub Command1_Click()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset("QueryB", dbOpenDynaset, dbReadOnly)

This part runs ok, so I think that it recognizes the two flds on my Form (fld3 and fld4). Just for grins, I renamed them but left the Between clause in QueryA the same. As expected, Access asked me to enter the values so it could run QueryA.

I apologize for the length of this message, but I wanted to describe the problem and what I have tried to do to resolve it. This problem has me stumped. Anyone see what I am doing wrong?

Thanks so much for your time!

dz
dzaccess@yahoo.com

 

[lupins46]

My guess would be that in the query what is being passed is not the field name but the field contents.

Assuming Access Query Grid then we should be expecting:

mymedian:Median("fldname", "tblname")

what might be happening is:
mymedian:Median([fldname], [tblname])

To see what is actually going on put a msgbox :
msgbox strQuery
immediately after the statement which sets the strQuery string.

 

[dhookom]

lupins46,
Good guess and trouble-shooting suggestion.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[FoxProProgrammer]

lupins46,

Thank you for your reply. I am anxious to try your suggestion on Monday, but unfortunately have my doubts that this is the problem. I copied the string (strQuery) that got created in my Median function to the Query Builder. It ran fine from there, which confused me even more. Just curious, why it would work from the Query Builder and not within the structure of his database.

Your suggestion reminded me about a few other things that I did while troubleshooting this issue:

* Before I created the form and my Median function, Access of course displayed a dialog box for the user to enter fld3 and fld4 (parameter queryA). After I created my Median function, but before I created the to enter fld3 and fld4, it appeared that Access ran the Median function before it asked for fld3 and fld4. The reason that I say this is because I set a break point in the Median function and it got there before it asked for fld3 and fld4. When I clicked End at the End/Debug prompt, it asked for fld3 and fld4. That made me think that it was bombing because it could not run QueryA, which is the reason that I created the form. I figured that if timing or order of precedence was the issue, it would be resolved by making fld3 and fld4 available before it ran the Median function. I think that is true because after I created the form, Access no longer displays the dialog box to enter fld3 and fld4, even after I press the End button on the End/Debug window.

* I forgot to mention that TableA is a linked table, which is why I can't run it from home. I don't have access to the company's network drive from home, but I do have a test case set up on my laptop with exactly the same structure (QueryA with the Median function getting data from QueryB, which gets data from a Table in my database.

* Another important point that I forgot to mention is that the Median function works within my colleagues database IF he converts QueryA to a Make Table Query. When it is a Select Query, the error occurs. The more I think about it (typing this out helped!) this is why my test case works and the error occurs when I run it within his database structure.

So the question to answer may be: Why does the Median function work when QueryA is a Make Table query and cause the error when it is a Select Query? It seems to me that there is some sort of timing problem here, like Access is running the Median function before QueryA has completed. That might be one explanation for why it works when QueryA creates a table instead of holding the data in memory, or however a Select query works.

Thanks again for your help!

dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

I tried lupins46's suggestion to put a MsgBox immediately after the strQuery string is built. Here is the string that is created. Note that I modified the code slightly to include the table name in brackets before the field name.

"Select [tbl name].[calc fld] From [tbl name] Where NOT IsNull([tbl name].[calc fld]) Order by [calc fld];"

This causes error Run-time error '3061': Too few parameters. Expected 2.

If I change QueryA to a Make Table Query, it works. Does anyone know what is going on here?

To summarize for any new readers:

1. TableA is on a network drive.
2. QueryA retrieves data from TableA
3. QueryB retrieves data from QueryA and calls the Median function.

This all works if QueryA is a Make Table Query or if QueryB retrives data from a Table. I get Run-time error 3061 if QueryA is a Select Query. The fact that this works when QueryA is a Make Table Query or Table validates that the SQL is correct (I think). I'm stumped.

Thanks for any suggestions to fix this problem or troubleshoot it.

dz
dzaccess@yahoo.com

 

[dhookom]

This sql "Select [tbl name].[calc fld] From [tbl name] Where NOT IsNull([tbl name].[calc fld]) Order by [calc fld];" is not right unless you have a table named "tbl name" and a field named "calc fld".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[FoxProProgrammer]

dhookom,

My understanding from the Access Help files is that tblname can be a Table or a Query. If my interpretation of that is incorrect, then how does one retrieve data from a Query, which is in effect a subquery. The Query does contain a field named "calc fld". Thanks a lot.

dz
dzaccess@yahoo.com

 

[dhookom]

dz,
The function you posted will not work. There are two references to "rs." that should be "rs2."

Fix the code and then open the debug window (press Ctrl+G). Then enter
? Median("ffff", "tttt")
where ffff is your field name and tttt is your table name.

Does this work?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[FoxProProgrammer]

Duane,

The error that you noted in my code occurred while I was troubleshooting and I had already fixed it. I changed rs to rs2 to make sure that two record sets weren't conflicting.

The Median function does not work in the Immediate window with my colleague's database, but it does work in the Immediate Window with my test case. I am still trying to figure out why the function works in my test case but not with his structure. I'm starting to think that there's a problem with his nested queries but it is hard to troubleshoot because of the complexity of his queries and fact that Access doesn't tell you which fields are missing. Expected 2 is pretty ambiguous. It would help if it would tell you which two it expected. Do you have any ideas on how to figure out which parameters are missing? It is not missing the two parameters that I passed to it.

Thanks,

dz
dzaccess@yahoo.com

 

[lupins46]

If you drop :
Select [tbl name].[calc fld] From [tbl name] Where NOT IsNull([tbl name].[calc fld]) Order by [calc fld]

into the SQL view of a new query does it run as you expect?

 

[dhookom]

The issue is that the recordset can't resolve the query parameters from the form controls. You could create a table TblA with two fields Fld3 and Fld4. Enter the values from the form into the single record in tblA and use a query with the new table and a where clause like:
WHERE [your field] Between [fld3] And [fld4]
Remove the references to the form controls. The code will now work.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[FoxProProgrammer]

lupins46: Access returns the error even when I create a new Query. The problem is as Duane noted: it can't resolve the two fields in the Between operator of the Where clause (Query A).

Duane: I suspected that those two fields were causing the problem, which is why I created a form to enter them. Maybe this is too complicated a question, but why doesn't it work if I enter the data on the form and tell Access where to find the data (with the Forms!formname!fldname syntax)? The following doesn't work, obviously, but I don't understand why.

SELECT flds...FROM tbl...WHERE somefld Between [Forms]![Form1]![startdt] And [Forms]![Form1]![enddt]

I tried to write the Query according to your suggestion, but couldn't get it to work. Here's what I tried:

SELECT TableA.fld1, TableA.fld2, (Select TblA.fld3 From TblA) As fld3, (Select TblA.fld4 From TblA) As fld4...FROM TableA...WHERE myfld Between [fld3] And [fld4];

Access asks me to enter fld3 and fld4. When I enter those two numbers, it runs the query and fld3 and fld4 are in the query output. I realize that I am using a field named in the Query in the Between operator. Is this why it doesn't work or did I do it differently than you suggested?

Thanks again for all your time.

dz
dzaccess@yahoo.com

 

[dhookom]

Assuming you have a single record in tblA that has two fields (fld3 and fld4) your query would be like:

SELECT TableA.fld1, TableA.fld2
FROM TableA, tblA
WHERE myfld Between [fld3] And [fld4];


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[FoxProProgrammer]

Duane,

I got it to work with a subquery in the Between operator. This is better than my last attempt anyway because I only want to retrieve fld3 and fld4 once. When fld3 and fld4 were in the query output, they were repeated for each record, which isn't correct.

SELECT [TableA].[fld1], [TableA].[fld2], ...FROM [TableA]
WHERE ((([TableA].myfld) Between (SELECT fld3 FROM TblA) And (SELECT fld4 FROM TblA)));

I'm still curious if you can explain why it doesn't work from a form, or point me to some reading material on this.

Also, is there a way to do this without a form? The guy that I'm helping out doesn't really want a form.

Thanks again for your help Duane and lupins46.

dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

Our paths crossed while typing. I tried it your way and it works as well. I'll use your SQL since it is less complex than mine. Thanks!

dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

By the way, I found an error in the Median function and added some basic error checking. Just thought I'd post it in case anyone tries to use it.

Public Function Median(fldname As String, tblname As String) As Single

Dim strQuery As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim numRecs, dataPt As Integer

' Get the data and sort it.
strQuery = "Select [" & fldname & "] From [" & tblname & "] Where NOT IsNull([" & fldname & "]) Order by [" & fldname & "];"
Set db = CurrentDb
Set rs = db.OpenRecordset(strQuery, dbOpenDynaset, dbReadOnly)

' Count the number of records and move to the beginning of record set.
' If no records, return median = 0
If rs.EOF = True And rs.BOF = True Then
    Median = 0
Else
    rs.MoveLast
    numRecs = rs.RecordCount
    rs.MoveFirst

    dataPt = Int((numRecs + 1) / 2)
    
    ' Find the Median
    If numRecs Mod 2 = 0 Then
        ' Even number of records.  Average the middle two values.
        rs.Move dataPt - 1
        Median = rs.Fields(fldname)
        rs.MoveNext
        Median = (Median + rs.Fields(fldname)) / 2
    Else
        ' Odd number of records.  Check if only one record.
        If dataPt = 1 Then
            rs.MoveFirst
        Else
            rs.Move dataPt - 1
        End If
        Median = rs.Fields(fldname)
    End If
End If

End Function

dz
dzaccess@yahoo.com

 

[dhookom]

I'm not sure why the recordset can't resolve the form control values. There is a way of specifying query parameters in the code but I am rusty with that and think it involves referencing a query rather than possibly a table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[FoxProProgrammer]

It's strange because I have often used form controls to provide values for parameter queries. I don't understand why it isn't working in this case, especially since the built in aggregate functions such as Max, Min, Count, etc work. Access displays a dialog box to enter fld3 and fld4 and then goes off and runs the query. The timing of when UDFs run in a Query must be different than built in functions. My guess is something in the query is causing this problem. His query includes calculated fields that are used to calculate other fields in the same query. I didn't even know that Access could handle that properly, but for some reason it does. Thanks again for your help.

dz
dzaccess@yahoo.com