VBA Function Median Results not accurate

[sross81]

Hello,

I have a median function that has worked well up until today. We passed a new data set to it and just for testing we copied the raw data into excel as we did with the other data sets and this time the median in Access was 250 intead of 249 that it is in excel.

Has anyone ever experienced this? I am just looking for some direction on what I should look into. We wondered if it has to do with the odd and even numbers in the total data set. This data set has 2207 records, but it has worked with other odd total records.

Function MedianF(pQuery As String, pfield As String) As Long

Dim rs As Recordset
Dim strSQL As String
Dim n As Long
Dim sglHold As Long
strSQL = "SELECT " & pfield & " from " & pQuery & " WHERE " & pfield & ">0 Order by " & pfield & ";"
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

 

[sross81]

In case anyone was following this the problem had to do with me excluding the 0's in the where clause. It was looking at my data set as an even number because the total record set added up to smaller than I was exporting out because I was excluding the 0 records in the export to excel when I was testing. I removed the entire where clause and it solved my problem.