Errors when using a median calculating function

[PaulA]

When trying to use functions that calculate the median (which I found through this forum), I was getting a "Too Few Parameters. Expected 1" error.

As the query which is to use the median function itself queries a parameter query, I thought that was the problem so I tried declaring that query and seting the parameter.

I am now getting the "Object variable or With block variable not set" error.

Any ideas or suggestions? Thanks!

Here is the code:

ublic Function GetMedian(FieldName As String, _
TableName As String) As Variant

Dim db As Database
Dim rs As Recordset
Dim firstVal As Double, recCount As Long
Dim qdf As QueryDef


Set qdf = db.QueryDefs("qryRpt_Main_PtDemographics_Summary")


qdf.Parameters(0) = [Forms]![frmPatientManagementReportMenu]![txtEndDate]

Set rs = CurrentDb.OpenRecordset("Select " & FieldName & " From " & TableName & " Order By " & FieldName & ";", dbOpenSnapshot)

recCount = rs.RecordCount

If recCount < 2 Then
Select Case recCount
Case 0
GetMedian = Null
Case 1
rs.MoveFirst
GetMedian = rs!FieldName
End Select
Set rs = Nothing
Exit Function
End If

rs.MoveLast
rs.MoveFirst

If (recCount Mod 2) = 0 Then
rs.Move (recCount / 2) - 1
firstVal = rs!FieldName
rs.MoveNext
GetMedian = (firstVal + rs!FieldName) / 2
Else
rs.Move Fix(recCount / 2) - 1
GetMedian = rs!FieldName
End If

Set rs = Nothing
End Function

 

[MarkK]

Understanding some else's code is not trivial. Specify the line on which the error occurs. Wrap your code in

[code]code tags[/code]

so indents are preserved

Public Function GetMedian(FieldName As String, TableName As String) As Variant
  Dim db As Database
  Dim rs As Recordset
  Dim firstVal As Double, recCount As Long
  Dim qdf As QueryDef
[COLOR="Green"]  'etc...[/COLOR]
End Function

And I'm playing around with these PHP tags, seeing what they do...

Sub Test1032984760937()
   Debug.Print TimedPopup("Greetings!!!", 2, "Hello World", vbYesNo + vbInformation)
End Sub

Public Function TimedPopup(Message As String, secondstowait As Long, title As String, options As Long)
   Dim tmp As New IWshRuntimeLibrary.WshShell
   'Set tmp = CreateObject("WScript.Shell")
   TimedPopup = tmp.Popup(Message, secondstowait, title, options)
End Function

Cheers,