Problems with Median function

[Kotartidzo]

I am trying to calculate the Median value for a field in a grouped report. The wierd thing is it works in my beta-test version but not in my production version. I am assuming I am missing some setting in my production version. I have confirmed my VBA references match in both versions. I am attaching a portion of the module. Any comments would be appreciated.

Public Function Median(TableName As String, FieldName As String, Optional Condition As String = vbNullString)
Dim str As String
Dim rst As DAO.Recordset
Dim n As Long
Dim x As Variant
Dim Centr As Variant

Dim db As Database: Set db = CurrentDb

str = " FROM " & TableName

If vbNullString = Condition Then
Else
str = str & " WHERE " & Condition
End If

'*** the following line of code is where the error keeps comming up. ***
Set rst = db.OpenRecordset("SELECT " & FieldName & str & " ORDER BY " & FieldName, dbOpenForwardOnly, dbReadOnly)

'calculate the number of records in the dataset
n = CurrentDb.OpenRecordset("SELECT COUNT(*)" & str).Fields(0).Value

***** the next line is the call statement in the report
=Median("qWEDnetEcoData","[Lead-timeVariance]")

I get the following error message.

Run time error "3061" Too few parameters. Expected 3.

 

[dhookom]

What is the SQL view of qWEDnetEcoData? Does it have any criteria?
I would change a line of your code to

    Dim db As DAO.Database
    Set db = CurrentDb

Duane
Hook'D on Access
MS Access MVP

 

[Kotartidzo]

I tried this replacement with no affect. My actual call statement within the report is:
=IIf([CountMeSum]=0,0,IIf([CountMeSum]=1,[LeadTimeVariance],Median("qWEDnetEcoData","[LeadTimeVariance]","[CountMe] = 1 and Month([Plan Due Date]) = " & [ForMonth] & " and [FunctionalArea] = '" & [ForFunctionalArea] & "' and [Activity] = '" & [ForActivity] & "'")))

Where:
- qWEDnetEcoData is the underlying query to the report
- LeadTimeVariance is the field containing the values to calculate the Median value
- CountMeSum totals a logic field which evaluates to 1 = count the data, 0 = don't count the data
- ForMonth is a Report Group object
- ForFunctionalArea is a Report Group object
- For Activity is a Report Group object

I did not include these before because the error doesn't seem to be afffected by the conditions passed to the function. The error occurs with, or without the conditions.

The LeadTimeVariance is a data field in the detail section. If there are less than 2 records the formula uses a 0, or the LeadTimeVariance value. The function is called only if there are two or more records to pass to the function.

 

[dhookom]

I'm not sure why you chose to ignore my first question to you

[qWEDnetEcoData] must have at least these fields [LeadTimeVariance], [CountMe], [Plan Due Date], [FunctionalArea], and [Activity]

Are any of the values sent into the function Null?

Duane
Hook'D on Access
MS Access MVP

 

[Kotartidzo]

Duane, I apologize for missing your first question. Although I use SQL I am not used to thinking of queries in SQL view? Attached is the SQL code for the query. We do display null records for the LeadTimeVariance field but I was assuming I would filter those out in the Median function using the CountMe = 1 criteria.


SELECT Month([Plan Due Date]) AS ForMonth, [WEDnet ECO Data].[Part Number], [WEDnet ECO Data].[Process Contol Code], [WEDnet ECO Data].[New or Revised], [WEDnet ECO Data].Description, [WEDnet ECO Data].[EN Number], [WEDnet ECO Data].Family, [WEDnet ECO Data].Analyst, [WEDnet ECO Data].[Date EN Received], [WEDnet ECO Data].[Date EN Processed], [WEDnet ECO Data].[Planned Imp Date], [WEDnet ECO Data].Activity, [WEDnet ECO Data].[Activity Owner], IIf(IsNull([Plan Due Date]),0,DateAdd("ww",[Lead-time (weeks)]*-1,[Plan Due Date])) AS PlanStartDt, IIf(IsNull([Plan Due Date]),0,DateDiff("d",[PlanStartDt],[Plan Due Date],2,1)) AS [Lead-timePlanlDays], [WEDnet ECO Data].[Lead-time (weeks)], [WEDnet ECO Data].[Plan Due Date], DateDiff("d",[PlanStartDt],[Actual Date],2,1) AS [Lead-timeActualDays], [WEDnet ECO Data].[Actual Date], IIf(IsNull([Actual Date]),IIf([Plan Due Date]<DateSerial(Year(Now()),Month(Now()),Day(Now())),DateDiff("d",[Plan Due Date],DateSerial(Year(Now()),Month(Now()),Day(Now())),2,1),Null),DateDiff("d",[Plan Due Date],[Actual Date],2,1)) AS LeadTimeVariance, DateDiff("d",[Date EN Received],[Date EN Processed],2,1) AS ProcessEcoLT, tActivitySelector.ShowData, tNameDeptList.FunctionalArea, IIf(IsNull([Actual Date]),IIf([Plan Due Date]<DateSerial(Year(Now()),Month(Now()),Day(Now())),1,0),1) AS CountMe, Forms!MENU!WorkDays AS NbrWorkDays
FROM ([WEDnet ECO Data] INNER JOIN tActivitySelector ON [WEDnet ECO Data].Activity = tActivitySelector.Activity) LEFT JOIN tNameDeptList ON [WEDnet ECO Data].[Activity Owner] = tNameDeptList.ActivityOwner
WHERE ((([WEDnet ECO Data].[Plan Due Date]) Between [Forms]![MENU]![StartDate] And [Forms]![MENU]![EndDate]) AND ((tActivitySelector.ShowData)=True))
ORDER BY Month([Plan Due Date]);

 

[dhookom]

I believe your criteria referencing the form controls is the issue. I would try replace the references with actual dates to see if the function works or maybe returns a different error message. You would also need to try replace Forms!MENU!WorkDays with an actual value.

The error "Run time error "3061" Too few parameters. Expected [red]3[/red]." most likely references the three "[Forms]!..."

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

Looks like you need

Set rst = db.OpenRecordset( _
"SELECT " & FieldName [red] & " FROM " & TableName [/red]& str & " ORDER BY " & FieldName, dbOpenForwardOnly, dbReadOnly)

 

[dhookom]

Golom,
I don't think so since the original code already has:

       str = " FROM " & TableName

I find the naming of the memory variables and arguments a bit confusing.

Duane
Hook'D on Access
MS Access MVP

 

[Kotartidzo]

Thanks dhookom and Golom for your feedback. Since my original search for a Median calculation returned dozens of options I am trying other alternatives to see if they will work for my needs.