John Spencer said:You can try the following VBA function. Copy it and paste it into a module.
You should be able to use it in a query or as the source for a control.
It works similarly to the DCount function that is built into VBA
Sample calls:
fGetMode("CountyOfResidence","Patients")
fGetMode("CountyofResidence","Patients", "LastName = 'Jones' ")
Public Function fGetMode(strField As String, _
strTable As String, _
Optional strWhere As String = "", _
Optional tfValue As Boolean = True) As Variant
'===============================================
' Procedure : fGetMode
' Created : 2/1/2007
' Copyright : 2007 Copyright by John Spencer
' Author : John Spencer
' Purpose : Return the MODE from a table or query
' Arguments : strField Name of field to return values from
' strTable name of table or saved query to use as source
' strWhere Optional where clause
' tfValue Return Values or Return Count
' False returns the Count of items in the mode
' Returns : Most frequently found value (or values in case of ties)
' "#Error#" if an error occurs
' # of Matches if more than 5 items are returned
' Permission to use is granted to all as long as this header information
' is included.
'===============================================
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
On Error GoTo fGetMode_Error
strSQL = "SELECT TOP 1 [" & strField & "] " _
& ", Count([" & strField & "])" _
& " FROM [" & strTable & "] " _
& " WHERE [" & strField & "] is not null"
' If there is a where clause then add it in
If Len(strWhere) > 0 Then
strSQL = strSQL & " AND " & strWhere
End If
strSQL = strSQL & " GROUP BY [" & strField & "] " _
& " ORDER BY Count([" & strField & "]) DESC;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
'Return values depending on number of records returned
With rs
If .RecordCount > 0 Then
.MoveLast
End If
'Return the number of items in mode
If tfValue = False Then
If .RecordCount < 1 Then
fGetMode = 0
Else
fGetMode = rs(1)
End If
'Return the value(s) of the items in mode
Else
Select Case rs.RecordCount
Case Is < 1
fGetMode = Null
Case 1 'Only one item found
fGetMode = rs(0) & vbNullString
'Multiple Matches Found
Case Is < 6 'Return concatenated string
strSQL = vbNullString 'initialize string
.MoveFirst
'Concatenate all the values
While Not .EOF
strSQL = strSQL & rs(0) & "; "
.MoveNext
Wend
'trim last two characters and return value
fGetMode = Left(strSQL, Len(strSQL) - 2)
Case Else 'Multiple items found as the mode
fGetMode = .RecordCount & " Matches "
End Select
End If
End With
fGetMode_EXIT:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
fGetMode_Error:
fGetMode = "#Error#"
Resume fGetMode_EXIT
End Function
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Al Campagna said:Melissa,
What do you mean by...
Please explain in more detail... some sample data would be helpful.
(What "option" data you have vs. what you want to see on the report.)
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions
"Find a job that you love, and you'll never work a day in your life."
Melissa said:Thank you so much but I can't get it working. It keeps giving me a #NAME?
error. It is highly likely that I'm messing something up because Access is
not my forte. Is there any tips on what I might be doing wrong?
John Spencer said:You can try the following VBA function. Copy it and paste it into a
module.
You should be able to use it in a query or as the source for a control.
It works similarly to the DCount function that is built into VBA
Sample calls:
fGetMode("CountyOfResidence","Patients")
fGetMode("CountyofResidence","Patients", "LastName = 'Jones' ")
Public Function fGetMode(strField As String, _
strTable As String, _
Optional strWhere As String = "", _
Optional tfValue As Boolean = True) As Variant
'===============================================
' Procedure : fGetMode
' Created : 2/1/2007
' Copyright : 2007 Copyright by John Spencer
' Author : John Spencer
' Purpose : Return the MODE from a table or query
' Arguments : strField Name of field to return values from
' strTable name of table or saved query to use as source
' strWhere Optional where clause
' tfValue Return Values or Return Count
' False returns the Count of items in the mode
' Returns : Most frequently found value (or values in case of ties)
' "#Error#" if an error occurs
' # of Matches if more than 5 items are returned
' Permission to use is granted to all as long as this header information
' is included.
'===============================================
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
On Error GoTo fGetMode_Error
strSQL = "SELECT TOP 1 [" & strField & "] " _
& ", Count([" & strField & "])" _
& " FROM [" & strTable & "] " _
& " WHERE [" & strField & "] is not null"
' If there is a where clause then add it in
If Len(strWhere) > 0 Then
strSQL = strSQL & " AND " & strWhere
End If
strSQL = strSQL & " GROUP BY [" & strField & "] " _
& " ORDER BY Count([" & strField & "]) DESC;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
'Return values depending on number of records returned
With rs
If .RecordCount > 0 Then
.MoveLast
End If
'Return the number of items in mode
If tfValue = False Then
If .RecordCount < 1 Then
fGetMode = 0
Else
fGetMode = rs(1)
End If
'Return the value(s) of the items in mode
Else
Select Case rs.RecordCount
Case Is < 1
fGetMode = Null
Case 1 'Only one item found
fGetMode = rs(0) & vbNullString
'Multiple Matches Found
Case Is < 6 'Return concatenated string
strSQL = vbNullString 'initialize string
.MoveFirst
'Concatenate all the values
While Not .EOF
strSQL = strSQL & rs(0) & "; "
.MoveNext
Wend
'trim last two characters and return value
fGetMode = Left(strSQL, Len(strSQL) - 2)
Case Else 'Multiple items found as the mode
fGetMode = .RecordCount & " Matches "
End Select
End If
End With
fGetMode_EXIT:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
fGetMode_Error:
fGetMode = "#Error#"
Resume fGetMode_EXIT
End Function
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Al Campagna said:Melissa,
What do you mean by...
most choosen option is in a field.
Please explain in more detail... some sample data would be helpful.
(What "option" data you have vs. what you want to see on the report.)
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions
"Find a job that you love, and you'll never work a day in your life."
I'm trying to put in a report what the most choosen option is in a
field.
My
instinct tells me to use mode but there is no mode. Any suggestions?
John Spencer said:Ok did you copy the code into a VBA module?
If you did, did you name the module something different than the function?
You should.
After you copy it into a VBA Module, select Debug: Compile from the menu.
If you get no errors at this point, then the problem is likely related to
where or how you are using the function.
Are you using this in a query that is the source for your report? Or are
you using it as a control source in the report?
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Melissa said:Thank you so much but I can't get it working. It keeps giving me a #NAME?
error. It is highly likely that I'm messing something up because Access is
not my forte. Is there any tips on what I might be doing wrong?
John Spencer said:You can try the following VBA function. Copy it and paste it into a
module.
You should be able to use it in a query or as the source for a control.
It works similarly to the DCount function that is built into VBA
Sample calls:
fGetMode("CountyOfResidence","Patients")
fGetMode("CountyofResidence","Patients", "LastName = 'Jones' ")
Public Function fGetMode(strField As String, _
strTable As String, _
Optional strWhere As String = "", _
Optional tfValue As Boolean = True) As Variant
'===============================================
' Procedure : fGetMode
' Created : 2/1/2007
' Copyright : 2007 Copyright by John Spencer
' Author : John Spencer
' Purpose : Return the MODE from a table or query
' Arguments : strField Name of field to return values from
' strTable name of table or saved query to use as source
' strWhere Optional where clause
' tfValue Return Values or Return Count
' False returns the Count of items in the mode
' Returns : Most frequently found value (or values in case of ties)
' "#Error#" if an error occurs
' # of Matches if more than 5 items are returned
' Permission to use is granted to all as long as this header information
' is included.
'===============================================
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
On Error GoTo fGetMode_Error
strSQL = "SELECT TOP 1 [" & strField & "] " _
& ", Count([" & strField & "])" _
& " FROM [" & strTable & "] " _
& " WHERE [" & strField & "] is not null"
' If there is a where clause then add it in
If Len(strWhere) > 0 Then
strSQL = strSQL & " AND " & strWhere
End If
strSQL = strSQL & " GROUP BY [" & strField & "] " _
& " ORDER BY Count([" & strField & "]) DESC;"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
'Return values depending on number of records returned
With rs
If .RecordCount > 0 Then
.MoveLast
End If
'Return the number of items in mode
If tfValue = False Then
If .RecordCount < 1 Then
fGetMode = 0
Else
fGetMode = rs(1)
End If
'Return the value(s) of the items in mode
Else
Select Case rs.RecordCount
Case Is < 1
fGetMode = Null
Case 1 'Only one item found
fGetMode = rs(0) & vbNullString
'Multiple Matches Found
Case Is < 6 'Return concatenated string
strSQL = vbNullString 'initialize string
.MoveFirst
'Concatenate all the values
While Not .EOF
strSQL = strSQL & rs(0) & "; "
.MoveNext
Wend
'trim last two characters and return value
fGetMode = Left(strSQL, Len(strSQL) - 2)
Case Else 'Multiple items found as the mode
fGetMode = .RecordCount & " Matches "
End Select
End If
End With
fGetMode_EXIT:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
fGetMode_Error:
fGetMode = "#Error#"
Resume fGetMode_EXIT
End Function
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Melissa,
What do you mean by...
most choosen option is in a field.
Please explain in more detail... some sample data would be helpful.
(What "option" data you have vs. what you want to see on the report.)
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions
"Find a job that you love, and you'll never work a day in your life."
I'm trying to put in a report what the most choosen option is in a
field.
My
instinct tells me to use mode but there is no mode. Any suggestions?
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.