mode function

[DAVINA]

I know how to get the min., max., average funtions but, what is the function that will give me the most common value is a field (mathmatically it's is called "mode&quot
Example: I have 4 values in a field they are: 20, 16, 20, 3. The mode of that is 20.
Thanks in advance for your help.

 

[tlbroadbent]

Check thread705-157495. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[MichaelRed]

I have two "functions" for this (DO NOT ASK WHY!)

First. Simple.

Public Function basRsMode(rsName As String, FldName As String) As Variant

    'Michael Red, 11/30/2001
    'Returns the Number/count of the (Stastical) Mode
    'Sample Usage:
    '? basRsMode("tblAttendance", "Hr1")
    '13
    '? basRsMode("tblConCatLang", "Comp")
    '2


    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    Set dbs = CurrentDb

    strSQL = "Select Top 1 Count(" & FldName & ") As MyMode "
    strSQL = strSQL & "From " & rsName & " "
    strSQL = strSQL & "Group By " & FldName & " "
    strSQL = strSQL & ";"

    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    
    basRsMode = rst!MyMode

End Function

Second. More options to confuse and Confound.

Public Function basMode(MyField As String, _
                        MyTable As String, _
                        Optional MyRel As String = "", _
                        Optional MyCriteria As String = "", _
                        Optional Delim As String = "") As Variant

    'Michael Red, 8/15/00
    'To Return the Mode of a field within a recordset.
    'Note: the optional parameter Delim is NOT implemented

    'MyRel is one of the VB relational operators (&quot;=&quot;, &quot;>=&quot;, &quot;<=&quot;, &quot;<>&quot;, &quot;>&quot;, &quot;<&quot;)
    'MyCriteria is the normal &quot;Where Clause&quot; (withouth the &quot;Where&quot;)

    'Sample Usage:
    '? basMode(&quot;Hr1&quot;, &quot;tblAttendance&quot;)
    'P

    '? basMode(&quot;Comp&quot;, &quot;tblConCatLang&quot;)
    'c5678

    Dim sql As String
    Dim rst As DAO.Recordset
    sql = &quot;Select &quot; & MyField & &quot;&quot;

    sql = sql & &quot;, Count(&quot; & MyField
    sql = sql & &quot;) As FieldCount &quot;
    sql = sql & &quot;From &quot; & MyTable
    If (MyCriteria <> &quot;&quot;) Then
       sql = sql & &quot; Where &quot; & &quot; &quot; & MyField & &quot; &quot;
       sql = sql & MyRel & &quot; &quot; & Chr(34) & MyCriteria & Chr(34)
    End If

    sql = sql & &quot; Group By &quot; & MyField
    sql = sql & &quot; Order By Count(&quot; & MyField & &quot;) Desc;&quot;


    Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

    If rst.EOF = True And rst.BOF = True Then
       basMode = &quot;&quot;
    Else
       rst.MoveFirst
       basMode = rst(MyField)
    End If

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over