Any particular reason the DB is being sent via PM instead of posting within the thread? The thread cannot be followed or be useful to others without the DB.
Any particular reason the DB is being sent via PM instead of posting within the thread? The thread cannot be followed or be useful to others without the DB.
Apologies for the db being sent via PM it was necessary, I will be posting a complete solution post with details once the fix is complete.
It would be easiest for me if you give me a report and a process to open it with the appropriate criteria.
Shipping report between 4/1 n 5/10 as long as there are duplicate names under 1 condition you should get your result on the report if it works
Try this, Allen's code slightly tweaked. Changes in red, if I remembered them all:
Code:Public Function ConcatRelatedCount(strField As String, _ strTable As String, _ Optional strWhere As String, _ Optional strOrderBy As String, _ Optional strSeparator = vbCrLf) As Variant 'Seperater default tff = Line Break = vbCrLf On Error GoTo Err_Handler 'Purpose: Generate a concatenated string of related records. 'Return: String variant, or Null if no matches. 'Arguments: strField = name of field to get results from and concatenate. ' strTable = name of a table or query. ' strWhere = WHERE clause to choose the right values. ' strOrderBy = ORDER BY clause, for sorting the values. ' strSeparator = characters to use between the concatenated values. 'Notes: 1. Use square brackets around field/table names with spaces or odd characters. ' 2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot. ' 3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs. ' 4. Returning more than 255 characters to a recordset triggers this Access bug: ' http://allenbrowne.com/bug-16.html Dim rs As DAO.Recordset 'Related records Dim rsMV As DAO.Recordset 'Multi-valued field recordset Dim strSQL As String 'SQL statement Dim strOut As String 'Output string to concatenate to. Dim lngLen As Long 'Length of string. Dim bIsMultiValue As Boolean 'Flag if strField is a multi-valued field. 'Initialize to Null ConcatRelatedCount = Null 'Build SQL string, and get the records. strSQL = "SELECT" & strField & ", Count(*) As HowMany FROM " & strTable ' + Count(*) As "Select" If strWhere <> vbNullString Then strSQL = strSQL & " WHERE " & strWhere 'Where End If strSQL = strSQL & "GROUP BY " & strField If strOrderBy <> vbNullString Then strSQL = strSQL & " ORDER BY" & strOrderBy 'Order By End If Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset) 'Determine if the requested field is multi-valued (Type is above 100.) bIsMultiValue = (rs(0).Type > 100) 'Loop through the matching records Do While Not rs.EOF If bIsMultiValue Then 'For multi-valued field, loop through the values Set rsMV = rs(0).Value Do While Not rsMV.EOF If Not IsNull(rsMV(0)) Then strOut = strOut & rsMV(0) & strSeparator End If rsMV.MoveNext Loop Set rsMV = Nothing ElseIf Not IsNull(rs(0)) Then strOut = strOut & rs(0) & " " & rs(1) & "x" & strSeparator End If rs.MoveNext Loop rs.Close 'Return the string without the trailing separator. lngLen = Len(strOut) - Len(strSeparator) If lngLen > 0 Then ConcatRelatedCount = Left(strOut, lngLen) End If Exit_Handler: 'Clean up Set rsMV = Nothing Set rs = Nothing Exit Function Err_Handler: MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelatedCount()" Resume Exit_Handler End Function
This worked beautifully, Please advise in the code posted above I had to swap rs(1) and "x" to create the x2 vs 2x, Tonight I will post an explanation of the database what it does and what this problem solved using code examples and pictures from the database.
Thank you pbaldy, I am grateful beyond words. Apologies for those attempting to follow this thread and await a solution I will do my best tonight to make what happened a learning experience.
Please check back later tonight for the details, thanks again!
Okay so the DB was created to report employees for things they missed by conditioning them in a drop down list style form, very simple interface on that end and essentially all it is used for by employees. This allows management to print reports to see where work is needed through out the process.
The Problem: The reports would show 3 colums:
Condition type--------------# of times condition was reported-----------------Names of conditioned
The last column "Names of conditioned" is a ConcatRelated() formula that creates a list of all employees within a time period that were reported for that condition.
unfortunately it would list as such:
Bob, Bill, Ted, Bob, Mary
as you can see this is not easy to read and employees that were reported more than once showed up more than once and out of order so first we changed the equation to columned like such:
Bob
Bill
Ted
Bob
Mary
This worked but we still had the issue of multiple names out of order which in turn makes the report harder to read.
The solution was to make them appear with a multiplier next to the name as such
Bob x2
Ted x1
Bill x1
Mary x1
This was accomplished by adding the count(*) to the concat function as such:
The reports now show as desired and explained above thanks to Pbaldy's help and knowledge of Functions. I am going to mark this as solved, if you feel I have left anything out please ask and I will respond.Code:Public Function ConcatRelated(strField As String, _ strTable As String, _ Optional strWhere As String, _ Optional strOrderBy As String, _ Optional strSeparator = vbCrLf) As Variant 'Seperater default tff = Line Break = vbCrLf On Error GoTo Err_Handler 'Purpose: Generate a concatenated string of related records. 'Return: String variant, or Null if no matches. 'Arguments: strField = name of field to get results from and concatenate. ' strTable = name of a table or query. ' strWhere = WHERE clause to choose the right values. ' strOrderBy = ORDER BY clause, for sorting the values. ' strSeparator = characters to use between the concatenated values. 'Notes: 1. Use square brackets around field/table names with spaces or odd characters. ' 2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot. ' 3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs. ' 4. Returning more than 255 characters to a recordset triggers this Access bug: ' http://allenbrowne.com/bug-16.html Dim rs As DAO.Recordset 'Related records Dim rsMV As DAO.Recordset 'Multi-valued field recordset Dim strSQL As String 'SQL statement Dim strOut As String 'Output string to concatenate to. Dim lngLen As Long 'Length of string. Dim bIsMultiValue As Boolean 'Flag if strField is a multi-valued field. 'Initialize to Null ConcatRelated = Null 'Build SQL string, and get the records. strSQL = "SELECT" & strField & ", Count(*) As HowMany FROM " & strTable ' + Select If strWhere <> vbNullString Then strSQL = strSQL & " WHERE " & strWhere 'Where End If strSQL = strSQL & "GROUP BY" & strField If strOrderBy <> vbNullString Then strSQL = strSQL & " ORDER BY" & strOrderBy 'Order By End If Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset) 'Determine if the requested field is multi-valued (Type is above 100.) bIsMultiValue = (rs(0).Type > 100) 'Loop through the matching records Do While Not rs.EOF If bIsMultiValue Then 'For multi-valued field, loop through the values Set rsMV = rs(0).Value Do While Not rsMV.EOF If Not IsNull(rsMV(0)) Then strOut = strOut & rsMV(0) & strSeparator End If rsMV.MoveNext Loop Set rsMV = Nothing ElseIf Not IsNull(rs(0)) Then strOut = strOut & rs(0) & " " & "x" & rs(1) & strSeparator End If rs.MoveNext Loop rs.Close 'Return the string without the trailing separator. lngLen = Len(strOut) - Len(strSeparator) If lngLen > 0 Then ConcatRelated = Left(strOut, lngLen) End If Exit_Handler: 'Clean up Set rsMV = Nothing Set rs = Nothing Exit Function Err_Handler: MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()" Resume Exit_Handler End Function
Thank you everyone for your help and patience.