Access Concatenation of multiple Records into Single field Failing

Posted on 2009-02-20
Last Modified: 2013-11-27
I am using a Concatenation Function I have used for years and now suddenly it does not work.
I need to get all multiple records for an Admin into one single field.
I have a query I am using to stack records prior to concatenation

SELECT qry_30DaysExp.Functional_Admin AS Admin, "Manager - " & [Department_Manager] & Chr(13) & Chr(10) & "Contractor Name - " & [Contractor_LastName] & ", " & [Contractor_FirstName] & Chr(13) & Chr(10) & "Contractor ID - " & [Contractor_ID] & Chr(13) & Chr(10) & "Start Date - " & [Contractor_Orig_StDate] & Chr(13) & Chr(10) & "End Date - " & [Contractor_End_Date] & Chr(13) & Chr(10) & "Hourly Rate - " & Format([Onsite_Hrly_Rate],"Currency") & Chr(13) & Chr(10) & "Keyfob Provided - " & Format([Keyfob_Provided],"True/False") & Chr(13) & Chr(10) & "Keyfob Returned - " & Format([Keyfob_Returned],"True/False") & Chr(13) & Chr(10) & "Badge Provided - " & Format([Badge_Provided],"True/False") & Chr(13) & Chr(10) & "Badge Returned - " & Format([Badge_Returned],"True/False") & Chr(13) & Chr(10) & "Laptop Provided - " & Format([Laptop_Provided],"True/False") & Chr(13) & Chr(10) & "Laptop Returned - " & Format([Laptop_Returned],"True/False") AS Contractor_Exp_String
FROM qry_30DaysExp
ORDER BY qry_30DaysExp.Functional_Admin;

Results are Perfect
Manager - Doe, John
Contractor Name - Doe, Ann
Contractor ID - 370
Start Date - 8/18/2008
End Date - 2/27/2009
Hourly Rate - $85.00
Keyfob Provided - False
Keyfob Returned - False
Badge Provided - False
Badge Returned - False
Laptop Provided - False
Laptop Returned - False

However when I use the function in a query this way ContExpList_CompleteOrig: IIf([Admin] Is Not Null,GetFieldFromMultiRecords(2,[Admin],"Qry_Concatenated_Exp_Contractors_Base","Admin","Contractor_Exp_String"),"")

 (code for function in code section) it returns the following

Manager - Doe, John
Contractor Name - Doe, Ann
Contractor ID - 370
Start Date - 8/18/2008
End Date - 2/27/2009
Hourly Rate - $85.00
Keyfob Provided - False
Keyfob Returned - False
Badge Provided - False
Badge Returned - False
Laptop Provisì@üsì@L#$vL#@L#@

If I shorten the number of fields I am using to just through Badge Provided everything works fine.

I need this concatenation to fully work.  The results have to be placed on the body of an e-mail.

What can I do to fix this?  Thanks

Function GetFieldFromMultiRecords(pbytKeyType As Byte, pvarID As Variant, pstrTable As String, pstrIDField As String, pstrMergeField As String) As String

On Error GoTo ErrHandle_Err

Dim db As DAO.Database

Dim rst As DAO.Recordset

Dim strReturnValue As String

Dim strWHERE As String

   strWHERE = " WHERE [" & pstrIDField & "]="

   Select Case pbytKeyType

       Case 1: strWHERE = strWHERE & pvarID

       Case 2: strWHERE = strWHERE & Chr(34) & pvarID & Chr(34)

       Case 3: strWHERE = strWHERE & "#" & Format(CDate(pvarID), "MM/DD/YYYY HH:NN:SS") & "#"

   End Select


   Set db = CurrentDb()


   Set rst = db.OpenRecordset("SELECT [" & pstrMergeField & "] FROM " & pstrTable & strWHERE)

   Do While Not rst.EOF

      strReturnValue = strReturnValue & vbCrLf & rst("[" & pstrMergeField & "]")

      'strReturnValue = strReturnValue & ", " & Chr(13) & Chr(10) & rst("[" & pstrMergeField & "]")




   If Len(strReturnValue) > 0 Then strReturnValue = Mid(strReturnValue, 3)


   GetFieldFromMultiRecords = strReturnValue


    Exit Function


    MsgBox Error$

    Resume ErrHandle_Exit

End Function

Open in new window

Question by:quizzer

    Author Comment

    Or is there a way to merge a multiple record query as text into the body of an e-mail.

    Email Code Below
    Function SendSMTP()
    On Error Resume Next
        Dim iMsg As Object
        Dim iConf As Object
        '********************* My Mail To Code
        Dim MyMailTo As String
        MyMailTo = [Forms]![Frm_30Days_Exp_Mgrs]![Txt_Functional_Admin_Email]
        '****** My Received String Code
        Dim MyReceivedString As String
    MyReceivedString = "Dear " & [Forms]![Frm_30Days_Exp_Mgrs]![Txt_Functional_Admin_First_Name] & ", " & Chr(13) & Chr(13)
    MyReceivedString = MyReceivedString + "According to our records the following contractor(s) are set to expire. Please reply to this email indicating whether or not you wish to extend or terminate the resource." & Chr(13) & Chr(13)
    '[Forms]![Frm_30Days_Exp_Mgrs]![Txt_ContExpList_Complete]  is where I place the Concatenated text into the body of the e-mail
    MyReceivedString = MyReceivedString + [Forms]![Frm_30Days_Exp_Mgrs]![Txt_ContExpList_Complete] & Chr(13) & Chr(13)
    MyReceivedString = MyReceivedString + "Thanks," & Chr(13) & Chr(13)
    MyReceivedString = MyReceivedString + "Purchasing" & Chr(13)
     '    Dim Flds As Variant
        Set iMsg = CreateObject("CDO.Message")
        Set iConf = CreateObject("CDO.Configuration")
            iConf.Load -1    ' CDO Source Defaults
            Set Flds = iConf.Fields
            With Flds
                .Item("") = 2
                .Item("") = "localhost"
               .Item("") = 25
            End With
        With iMsg
            Set .Configuration = iConf
            .To = MyMailTo
            .CC = ""
            .BCC = ""
            .From = ""
            .Subject = "Expiring Contractor Notice"
            .Textbody = MyReceivedString
        End With
    MsgBox "Expiring Contractor Notice Has Been Sent To " + MyMailTo
    End Function

    Open in new window

    LVL 119

    Accepted Solution

    you are exceeding the 255 character limit..
    what you can do is to use recordsets

    dim rs as dao.recordsets
    set rs=currentdb.openrecordset("qry_30DaysExp")

    if rs.eof then exit sub

    do until rs.eof

    ' format the body of the text here




    Author Comment

    How do I do this specifically in my query?

    I am Grouping by the Field Admin, needing the fields in the query below SQL
    LVL 119

    Expert Comment

    by:Rey Obrero
    all the fields will be available in the recordset and you can refer to them like

    rs![Department_Manager] -- this will contain the value of the manager Doe, John


    Author Comment

    Do I call this function in a query and will it return all rows with the concatenated field?

    Author Comment

    Or do i need to append this recordset data into a Memo field and have the mail function refer to this field.  PLEASE HELP VERY IMPORTANT FOR ME TO FIGURE THIS OUT!!!!!
    LVL 119

    Expert Comment

    by:Rey Obrero
    when you use the recordset approach,
    *you are not going to apply it to your query
    * you will do the formatting of the text for body of your mail while iterating thru the recordset

    Author Comment

    Also tried a Function Using a Recordset and I still get the 255 character error.  I thought recordsets would fix the problem
    Function Concatenate(pstrSQL As String, _
            Optional pstrDelim As String = vbCrLf) _
                As String
        'tblFamily with FamID as numeric primary key
        'tblFamMem with FamID, FirstName, DOB,...
        'return a comma separated list of FirstNames
        'for a FamID
        '    John, Mary, Susan
        'in a Query
        '(This SQL statement assumes FamID is numeric)
        'SELECT FamID,
        'Concatenate("SELECT FirstName FROM tblFamMem
        '     WHERE FamID =" & [FamID]) as FirstNames
        'FROM tblFamily
        'If the FamID is a string then the SQL would be
        'SELECT FamID,
        'Concatenate("SELECT FirstName FROM tblFamMem
        '     WHERE FamID =""" & [FamID] & """") as FirstNames
        'FROM tblFamily
        '======For DAO uncomment next 4 lines=======
        '======     comment out ADO below    =======
        'Dim db As DAO.Database
       ' Dim rs As DAO.Recordset
        'Set db = CurrentDb
        'Set rs = db.OpenRecordset(pstrSQL)
        '======For ADO uncomment next two lines=====
        '======     comment out DAO above     ======
        Dim rs As New ADODB.Recordset
        rs.Open pstrSQL, CurrentProject.Connection, _
                adOpenKeyset, adLockOptimistic
        Dim strConcat As String 'build return string
        With rs
            If Not .EOF Then
                Do While Not .EOF
                    strConcat = strConcat & _
                        .Fields(0) & pstrDelim
            End If
        End With
        Set rs = Nothing
        '====== uncomment next line for DAO ========
        'Set db = Nothing
        If Len(strConcat) > 0 Then
            strConcat = Left(strConcat, _
                Len(strConcat) - Len(pstrDelim))
        End If
        Concatenate = strConcat
    End Function

    Open in new window


    Author Comment

    If I use the query calling the Function Concatenate and append the results to a memo field it works, but it does not work purely in the query

    Concatenate("SELECT Barcode_Qty FROM QueryBase") AS Codes
    FROM QueryBase

    breaks on 255 characters


    Codes: Concatenate("SELECT TextData FROM Table1 WHERE Key =""" & [Key] & """")

    Where TextData in the Table is a Memo Field works

    Is there something in the Concatenate function that could be changed so that it accepts more than 255 characters

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now