Median Time

[carrietm]

I need to report the Median Time for each staff member. Could you please tell me how to tweak the module from Post=106449? That post has helped me with many reports but unfortunately the TAT (turn around time) on the original was for the whole department and not each individual.

[HansV]

If staff members are identified by a number field, say StaffID, you can use

=DMedian("TAT","mktblMedian","StaffID=" & [StaffID])

If they are identified by a text field, say StaffName, you can use

=DMedian("TAT","mktblMedian","StaffID=" & Chr(34) & [StaffID] & Chr(34))

Chr(34) is the double quote character ", used to enclose string values.

[carrietm]

Thank you Hans!

I'm getting a debug error on: Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

[carrietm]

I'm getting a debug error on: Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

[HansV]

Have you set a reference to the Microsoft DAO 3.6 Object Library if you have a .mdb file, or to the Microsoft Office n.0 Access database engine Object Library if you have a .adddb file?

[carrietm]

If I only understood that. lol

This is the module that I pasted in. Is it somewhere in here?

Function DMedian(FieldName As String, TableName As String, Optional WhereCondition As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim arr
Dim m As Long
Set dbs = CurrentDb
strSQL = "SELECT [" & FieldName & "] FROM [" & TableName & "]"
If WhereCondition <> "" Then
strSQL = strSQL & " WHERE " & WhereCondition
End If
strSQL = strSQL & " ORDER BY [" & FieldName & "]"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
arr = rst.GetRows(1000000) ' fetch at most 1,000,000 records
rst.Close
Set rst = Nothing
Set dbs = Nothing
m = UBound(arr, 2)
If m Mod 2 = 0 Then
DMedian = arr(0, m / 2)
Else
DMedian = (arr(0, (m + 1) / 2) + arr(0, (m - 1) / 2)) / 2
End If
End Function

[Rudi]

You need to activate a reference to a specific Library file under Tools, References in Access VBA.
See images...

1.jpg

The reference in my screenshot is 15.0. (Office 2013), yours might be 14.0 or 12.0, etc... based on your Office Version

2.jpg

[carrietm]

Hopefully my screen shot will show up.

I went into the properties of the database and it says that the Type of file is Microsoft Access Database (.accdb) Should I still click the Microsoft DAO 3.6 Object Library box? I don't see the MISSING: Utility.mda or the Microsoft Visual Basic for Applications Extensiblity... from the other screen shot. Does that matter?

Microsoft Visual Basic for Applications - QI Data - [Module1 (Code).jpg

[HansV]

Thanks - your screenshot looks fine, and it contains the correct references.

Does mktblMedian contain a field StaffID?

[carrietm]

Yes, it is text.

[HansV]

What was the exact text of the error message?

[carrietm]

I removed the text box yesterday because it was locking up and the yellow highlight was on: Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

I'm sorry, I must have done something wrong yesterday because today, the yellow highligh is on: arr = rst.GetRows(1000000) ' fetch at most 1,000,000 records

It still isn't working but different text is being highlighted today.

[HansV]

And what does the error message say now?

[carrietm]

The debug yellow highlight is on: arr = rst.GetRows(1000000) ' fetch at most 1,000,000 records

mktblMedian has 728 records with 12 staff members.

TAT is a Date/Time field formatted as Short Time.
StaffID is text.

[HansV]

Thank you, but what is the text on the error message dialog?

[carrietm]

This is the only message that I see:

Debug highlighted area.jpg

[HansV]

That is the highlighted line, but not the error message.

[carrietm]

I think I've found it. Is this what you need?

Run-Time Error.jpg

[HansV]

Thanks. Could you attach a zipped copy of the database?

[carrietm]

I was creating the zip file and while stripping the db down I realized that some of the appointments weren't assigned so [StaffID] had blanks. I deleted the blanks and the error went away. Whew! I'm glad that's fixed. I apologize for taking up so much of your time! Thank you for hanging in there with me!!!