UDF in a Query not working

[FoxProProgrammer]

I wrote a function that calculates the Median of a set of data. Here is the code.

Option Compare Database

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

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 & "] 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.
numRecs = rs.RecordCount
rs.MoveFirst

' Find the Median
dataPt = Int((numRecs + 1) / 2)

If dataPt Mod 2 = 0 Then
    ' Odd number of records.
    rs.Move (dataPt - 1)
    Median = rs.Fields(fldname)
Else
    ' 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
End If

End Function

The function works as long as tblname is a Table. I want to use the function in cases where tblname is a Query. Can this be done? It doesn't seem like it should matter if the the data comes from a Table or Query. I get "run time error 3061, Too few parameters. Expected 2" when tblname is a Query output. It works fine when tblname is a Table. Effectively I've got a subquery in the From Clause. If this is the problem, how can I get this done? Should I use a make table query instead, and pass the name of the table that got created to my Median function? I want to avoid that if possible.

Thanks,

dz
dzaccess@yahoo.com

 

[Snowcrash]

Hey

This error "run time error 3061, Too few parameters. Expected 2" indicates that a field in your query (and in this case 2) do not match the fields in your table.

Output the table name before you run the query to make sure you are passing the correct name and then output the query string after you generate it but before it fires to make sure the string is formatted correctly.

No reason it shouldn't work other than that.

Snowcrash

 

[FoxProProgrammer]

Thanks for your reply, snowcrash. I have been at a conference the last few days and just got back to look at this. I figured out what the problem is but don't yet know how to fix it. Here is a summary of the structure. The tables and queries are much larger than what I have shown here, but these are the salient parts.

TableA contains two fields: Fld1 and Fld2

Parameter QueryA retrieves data from TableA: (Fld1 - Fld2) AS CalcFld1

QueryB calculates the median of CalcFld1 by placing the following in the query:

Median("CalcFld1", "QueryA")

The problem, it appears, is that QueryA is a parameter query. When you run the query, Access asks you to enter two values, which it uses to process the query. When QueryB runs, my Median function is called before the two parameters are requested. So while I initially thought the run time error 3061 was due to a missing parameter for my Median function, it looks like the two parameters that are missing are for QueryA.

Do you see a way around this problem?

Thanks,


dz
dzaccess@yahoo.com

 

[PHV]

Have you tried to use a form for the 2 parameters ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FoxProProgrammer]

I suggested a form to the user, but he doesn't want one. He built up these really complicated queries using the query designer and doesn't want to spend any time developing a form. A form would definitely resolve the issue, but since he doesn't want one, I am hoping that there is a way to get this to work with his manual queries. Thanks for your suggestion.

dz
dzaccess@yahoo.com