Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Access Concatenation of multiple Records into Single field Failing

Posted on 2009-02-20
Medium Priority
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
  • 6
  • 3

Author Comment

ID: 23695835
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("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "localhost"
           .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        End With
    With iMsg
        Set .Configuration = iConf
        .To = MyMailTo
        .CC = ""
        .BCC = ""
        .From = "Test@Test.com"
        .Subject = "Expiring Contractor Notice"
        .Textbody = MyReceivedString
    End With
MsgBox "Expiring Contractor Notice Has Been Sent To " + MyMailTo
End Function

Open in new window

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1640 total points
ID: 23695991
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

ID: 23696154
How do I do this specifically in my query?

I am Grouping by the Field Admin, needing the fields in the query below SQL
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 23696405
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

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

Author Comment

ID: 23700374
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 23700457
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

ID: 23710546
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

ID: 23711084
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

580 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