Median with a date range called from a form

[DuckyMinotaur]

Hi, I'm using the following vba to calculate the Median, and the Median Absolute Deviation
Public Function DMedian(FieldName As String, _
TableName As String, _
Optional Criteria As Variant) As Variant

' Created by Roger J. Carlson
' Terms of use: You may use this function in any application, but
' it must include this notice.

'Returns the median of a given field in a given table.
'Returns -1 if no recordset is created

' You use this function much like the built-in Domain functions
' (DLookUp, DMax, and so on). That is, you must provide the
' 1) field name, 2) table name, and 3) a 'Where' Criteria.
' When used in an aggregate query, you MUST add each field
' in the GROUP BY clause into the into the Where Criteria
' of this function.

' See Help for more on Domain Aggregate functions.

On Error GoTo Err_Median

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim RowCount As Long
Dim LowMedian As Double, HighMedian As Double

'Open a recordset on the table.
Set db = CurrentDb
strSQL = "SELECT " & FieldName & " FROM " & TableName
If Not IsMissing(Criteria) Then
strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " & FieldName
Else
strSQL = strSQL & " ORDER BY " & FieldName
End If
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'Find the number of rows in the table.
rs.MoveLast
RowCount = rs.RecordCount
rs.MoveFirst

'Determine Even or Odd
If RowCount Mod 2 = 0 Then
'There is an even number of records. Determine the low and high
'values in the middle and average them.
rs.Move Int(RowCount / 2) - 1
LowMedian = rs(FieldName)
rs.Move 1
HighMedian = rs(FieldName)
'Return Median
DMedian = (LowMedian + HighMedian) / 2
Else
'There is an odd number of records. Return the value exactly in
'the middle.
rs.Move Int(RowCount / 2)
'Return Median
DMedian = rs(FieldName)
End If

Exit_Median:
'close recordset
rs.Close
Exit Function

Err_Median:
If Err.Number = 3075 Then
DMedian = 0
Resume Exit_Median
ElseIf Err.Number = 3021 Then
'EOF or BOF ie no recordset created
DMedian = -1
Resume Exit_Median
Else
MsgBox Err.Description
Resume Exit_Median
End If
End Function

Called using DMedian("Field", "Table", "[Group] ='" & Group & "'") As Median
From Table
Group By Group

However I also need to call the date range from a field in a form on the previous query so Date is Between [Forms]![Form]![Date_from] and [Forms]![Form]![Date_to]
As soon as I do this, it no longer works, is there any way around this?
I think I have to define it in the vba but I'm not sure how
Thanks

 

[The_Doc_Man]

Show us a VBA line that attempts to do what you wanted to do and also tell us the error that it throws at you. If you get an option to debug the function (usually offers the choice to terminate or debug), take the debug option and identify the offending line. That might help us see what is going on a bit better.

 

[DuckyMinotaur]

Dim qdf AS DAO.QueryDef
Set qdf = db.QueryDefs("previous query")
qdf.Parameters("[Forms]![Form]![Date_from]") = [Forms]![Form]![Date_from]
qdf.Parameters("[Forms]![Form]![Date_to]") = [Forms]![Form]![Date_to]
Set rs = qdf.OpenRecordset(strSQL)

maybe something like that, though I'm new to vba so don't know if its allowed
This throws up User-defined type not defined and highlights ", HighMedian As DoubleDim"

or if I have to say something like
qdf.Parameters("Date") = Between [Forms]![Form]![Date_from] And [Forms]![Form]![Date_to] (no idea about the syntax here)