Hi,

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
Exit Function
End If
If mCount = 1 Then
CalcMedian = DLookup(mField, mTable, mPKfield & " = " & pPKvalue)
Exit Function
End If
If CLng(mCount / 2) - (mCount / 2) < 0.0001 Then
'number is even
mHalfCount = CLng(mCount / 2)
mBoo = True
Else
mHalfCount = CLng(mCount / 2 + 0.5)
mBoo = False
End If
s = "SELECT " & mPKfield & ", " & mField _
& " FROM " & mTable _
& " WHERE " & mPKfield & " = " & pPKvalue _
& " ORDER BY " & mField
Set r = CurrentDb.OpenRecordset(s)
r.MoveFirst
r.Move (mHalfCount - 1)
If mBoo Then
mSum = Nz(r(mField), 0)
r.MoveNext
mSum = mSum + Nz(r(mField), 0)
CalcMedian = mSum / 2
Else
CalcMedian = Nz(r(mField), 0)
End If
End Function

One problem lies with your field names. It is for this reason we always recommend you never use spaces in field names. You need to insure that your field names are enclosed within brackets if there are spaces in the name.

mField = "[Reg to Close]"
mPKfield = "[Closing Month]"

Hi
Just on the off chance is that a "space" between Closing?Month
If so try putting some brackets around the fields name.
eg;
mPKfield = "[Closing Month]"

HTH's

Thank you for you replies. The bracketing did help, however, its still not giving me the median results (it was error, now its 0).

Someone mentioned to me that it could be the issue of Dcount, however, I am not extremely familiar with the parameters (if they are necessary).