I am trying to run a code for gaining access to a median. However, when I attempt to run the code it is giving me the following error msg:
It says "Run Time Error" 3075
Syntax error (missing operator) in query expression 'Count(Closing Month)'
Thank you in advanced for your help.
Here is the code:
Function CalcMedian(pPKvalue) As Double
Dim mCount As Long, mSumMid As Double, mTable As String, mField As String, mPKfield As String
Dim mHalfCount As Long, s As String, r As DAO.Recordset, mBoo As Boolean, mSum As Double
mTable = "T - Closings 015 - No Inspections"
mField = "Reg to Close"
mPKfield = "Closing Month"
mCount = Nz(DCount(mPKfield, mTable, mPKfield & "=" & pPKvalue), 0)
If mCount = 0 Then
CalcMedian = 0
If mCount = 1 Then
CalcMedian = DLookup(mField, mTable, mPKfield & " = " & pPKvalue)
If CLng(mCount / 2) - (mCount / 2) < 0.0001 Then
'number is even
mHalfCount = CLng(mCount / 2)
mBoo = True
mHalfCount = CLng(mCount / 2 + 0.5)
mBoo = False
s = "SELECT " & mPKfield & ", " & mField _
& " FROM " & mTable _
& " WHERE " & mPKfield & " = " & pPKvalue _
& " ORDER BY " & mField
Set r = CurrentDb.OpenRecordset(s)
r.Move (mHalfCount - 1)
If mBoo Then
mSum = Nz(r(mField), 0)
mSum = mSum + Nz(r(mField), 0)
CalcMedian = mSum / 2
CalcMedian = Nz(r(mField), 0)