Median Module Error

[dimsum1]

I was hoping someone could help out with a median module we are trying to get to work.
It fails at the rs.MoveLast step if the particular grouped values are all 0 values. As long as the result field has a value the code works perfectly. Anyone have a good way to get around this? In other words I want the median formula to ignore the calculation or put something like a 0 and move on to the next group. - Thanks

Function MedianF(pTable As String, pfield As String, Optional pgroup As String) As Single

Dim rs       As recordSet
Dim strSQL   As String
Dim n        As Integer
Dim sglHold  As Single
If Len(pgroup) > 0 Then
    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE analyte= '" & pgroup & "' and " & pfield & ">0 Order by " & pfield & ";"
Else
    strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
End If
Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveLast
    n = rs.RecordCount
    rs.Move -Int(n / 2)
    
    If n Mod 2 = 1 Then 'odd number of elements
       MedianF = rs(pfield)
    Else                'even number of elements
       sglHold = rs(pfield)
       rs.MoveNext
       sglHold = sglHold + rs(pfield)
       MedianF = sglHold / 2
    End If
    rs.Close
End Function

 

[ranman256]

At rs.Move -Int(n / 2) , rs.Move is not a command...maybe movenext.

 

[Darren Bartrup]

rs.Move -In(n/2) is a command.

Check that records are returned in the first place:

If rs.BOF and rs.EOF Then
 MsgBox "No Records Returned"
End If

expression.Move(Rows, StartBookmark)
expression A variable that represents a Recordset object.
Parameters

Name	Required/Optional	Data Type	Description
Rows	Required	Long	The number of rows the position will move. If rows is greater than 0, the position is moved forward (toward the end of the file). If rows is less than 0, the position is moved backward (toward the beginning of the file).
StartBookmark	Optional	Variant	A value identifying a bookmark. If you specify startbookmark, the move begins relative to this bookmark. Otherwise, Move begins from the current record.

<tbody>
</tbody>

 

[dimsum1]

Thanks guys I found alternative code that works but I'll try your suggestions and post back with the results.