I have a VBA code that I use to compute the median:
Expand|Select|Wrap|Line Numbers
- Option Compare Database
- Option Explicit
- Function DMedian(tName As String, fldName As String) As Single
- Dim MedianDB As DAO.Database
- Dim ssMedian As DAO.Recordset
- Dim RCount As Integer, i As Integer, x As Double, y As Double, _
- OffSet As Integer
- Set MedianDB = CurrentDb()
- Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
- "] FROM [" & tName & "] WHERE [" & fldName & _
- "] IS NOT NULL ORDER BY [" & fldName & "];")
- 'NOTE: To include nulls when calculating the median value, omit
- 'WHERE [" & fldName & "] IS NOT NULL from the example.
- ssMedian.MoveLast
- RCount% = ssMedian.RecordCount
- x = RCount Mod 2
- If x <> 0 Then
- OffSet = ((RCount + 1) / 2) - 2
- For i% = 0 To OffSet
- ssMedian.MovePrevious
- Next i
- DMedian = ssMedian(fldName)
- Else
- OffSet = (RCount / 2) - 2
- For i = 0 To OffSet
- ssMedian.MovePrevious
- Next i
- x = ssMedian(fldName)
- ssMedian.MovePrevious
- y = ssMedian(fldName)
- DMedian = (x + y) / 2
- End If
- If Not ssMedian Is Nothing Then
- ssMedian.Close
- Set ssMedian = Nothing
- End If
- Set MedianDB = Nothing
- End Function
My query takes the data in an unbound textbox(es) [qField] on a form [Search] and passes it on to a report.
At this time all my SQL conditions are in this format:
Expand|Select|Wrap|Line Numbers
- Like [Forms]![Search].[qField] & "*"
Thanks in Advance!