Workaround for Concatation Expression that Truncates a Memo Field

Access treats concatenated text or memo fields as text.  One possible workaround is to use a union query with your memo field to force the output to be memo. Try this:

SELECT tbl_ActionPlanUpdates.fld_ActionPlanNumber, [tbl_ActionPlanUpdates]![fld_ActionPlanTargetCloseNotes] FROM tbl_ActionPlanUpdates WHERE (False)
UNION ALL
SELECT tbl_ActionPlanUpdates.fld_ActionPlanNumber, "As of " & [tbl_ActionPlanUpdates]![fld_ActionPlanUpdateDate] & " >> " & [tbl_ActionPlanUpdates]![fld_ActionPlanTargetCloseNotes] AS exp_UpdateDateNotes
FROM tbl_ActionPlanUpdates
WHERE tbl_ActionPlanUpdates.fld_ActionPlanTargetCloseNotes & "" <> ""
ORDER BY tbl_ActionPlanUpdates.fld_ActionPlanNumber, tbl_ActionPlanUpdates.fld_ActionPlanUpdateDate DESC

Select allOpen in new window

What are you doing with the results of this query?

Memo fields normally get truncated when you try to use them in queries that include DISTINCT, or Group By clauses, but this particular query doesn't appear to have that issue.

They will also be truncated when used as a field in a listbox or combo box (max field width in those controls is 255), or if you try to simply use a TransferSpreadsheet to export this to EXCEL.

mbizup,

The solution yields the attached error message
New-Picture.jpg

Okay - try this variation:

SELECT tbl_ActionPlanUpdates.fld_ActionPlanNumber, [tbl_ActionPlanUpdates]![fld_ActionPlanTargetCloseNotes], tbl_ActionPlanUpdates.fld_ActionPlanUpdateDate  FROM tbl_ActionPlanUpdates WHERE (False)
UNION ALL
SELECT tbl_ActionPlanUpdates.fld_ActionPlanNumber, "As of " & [tbl_ActionPlanUpdates]![fld_ActionPlanUpdateDate] & " >> " & [tbl_ActionPlanUpdates]![fld_ActionPlanTargetCloseNotes] AS exp_UpdateDateNotes, tbl_ActionPlanUpdates.fld_ActionPlanUpdateDate 
FROM tbl_ActionPlanUpdates
WHERE tbl_ActionPlanUpdates.fld_ActionPlanTargetCloseNotes & "" <> ""
ORDER BY tbl_ActionPlanUpdates.fld_ActionPlanNumber, tbl_ActionPlanUpdates.fld_ActionPlanUpdateDate DESC

Select allOpen in new window

fyed,

I'm using this first query in a subsequent query:


This second query is used in a third query:


I did all this with the Access query builders - hence, why it's not pretty at all.

@songwritingcpa

I get the issue with the "pretty" and all of the ( ) in the joins.

It is the Group By clause in your 2nd query that is causing the truncation.  What I would consider is trying to see if you can remove the fnconcat( ) function from the 2nd query, and incorporate that into the 3rd, somehow.

mbizup,

exp_UpdateDateNotes yields no values (they look blank in datasheet view).  Also, I'm not sure if this is relevant or not -- the column ends up being titled "tbl_ActionPlanUpdates]![fld_ActionPlanTargetCloseNotes".

for readability (in code and in the ACCESS SQL query window) you might also want to consider inserting hard breaks in your SQL statements to make them more readable. Makes is significantly easier to read and edit in the Access SQL view:

SELECT qry_CurrentActionPlans_MaxUpdateDate.MaxOffld_ActionPlanUpdateDate
           , tbl_ActionPlans.fld_ActionPlanNumber
           , qry_CurrentActionPlans_MaxUpdateDate.exp_AttestUpdateContact
           , tbl_ActionPlans.fld_ActionPlanTitle
           , qry_CurrentActionPlans_Description.exp_FullDescription

Another suggestion would be to use aliases for your query and table names.

The group by in the second query is definitely problematic... however I believe the concatenation in the first query is at issue too.

Try this, independently of the outer queries

SELECT tbl_ActionPlanUpdates.fld_ActionPlanNumber, [tbl_ActionPlanUpdates]![fld_ActionPlanTargetCloseNotes] AS exp_UpdateDateNotes,  tbl_ActionPlanUpdates.fld_ActionPlanUpdateDate  FROM tbl_ActionPlanUpdates WHERE (False)
UNION ALL
SELECT tbl_ActionPlanUpdates.fld_ActionPlanNumber, "As of " & [tbl_ActionPlanUpdates]![fld_ActionPlanUpdateDate] & " >> " & [tbl_ActionPlanUpdates]![fld_ActionPlanTargetCloseNotes] AS exp_UpdateDateNotes, tbl_ActionPlanUpdates.fld_ActionPlanUpdateDate 
FROM tbl_ActionPlanUpdates
WHERE tbl_ActionPlanUpdates.fld_ActionPlanTargetCloseNotes <>IsEmpty([tbl_ActionPlanUpdates]![fld_ActionPlanTargetCloseNotes])
ORDER BY tbl_ActionPlanUpdates.fld_ActionPlanNumber, tbl_ActionPlanUpdates.fld_ActionPlanUpdateDate DESC

Select allOpen in new window

Anyhow - I'm going to step back and let Dale follow up here to avoid confusing you with two people independently working on different parts of your query.

Okay, y'all, don't hate me. I'm discovering that the problem isn't with the first query. The raw data appears truncated in some of the Excel files that I received from our global team members. I copy/paste these into the Access record, though when the Excel cell does exceed 255, the copy/paste still works. Indeed, as fyed claims, the problem is with the "Group By" query.

fyed,

How do you suggest that I overcome the truncation problem in the second query? The whole purpose of the second query is concatenating the many instances of exp_UpdateDateNotes into one field (grouped by fld_ActionPlanNumber) that can be used for other queries. If I tried to incorporate the concatenation into the third query, wouldn't I run into the same problem? (Not to mention, it would be that much harder to keep track of what was happening in a more complex query.)

See screenshots of the Access query builders for #2 and #3, if that helps overcome the mess of SQL.
2nd-Query---Group-By.jpg
3rd-Query.jpg

Miriam,

I've never tried that technique to force a field to a particular data type when doing a concatenation.  I'll have to play with that later.

@songwritingcpa,

Lets do this one step at a time.

When you run the first query, by itself, is the field concatenated?

If so, I would definitely try Miriam's technique of the union query.  My perception of what Miriam (mbizup) is trying to accomplish with the first part of the union is to force Access to interpret that 2nd field as a string (without concatenation).  I have not tried that, but it might work.

Personally, I would replace the IsEmpty part of that query with

WHERE tbl_ActionPlanUpdates.fld_ActionPlanTargetCloseNotes & "" <> ""

This syntax will take what is in the CloseNotes field and append an empty string to it so that you can compare it to an empty string.  If ...CloseNotes contains a NULL, attempting to compare it to anything will result in a NULL, so you need this type of context to make the test.  I see Miriam also used that syntax in one of her sample queries above as well.

After we have determined that the first part of the query works, then we will work on rewriting the 2nd and 3rd parts.

The two columns of my original query (that I first posted) and Miriam's union query (minus the third column) produce the same exact results -- same number of records, and same number of characters (per a word count in Word).

I have multiple dimensions of concatenation going, so I want to clarify.  The first query is concatenated in the simple sense.  For example, [Date] & " >> " & [Notes] works.

The second query is where the more complex "vertical" concatenation occurs, with the fConcatFld function.

Since the purpose of Query2 is to concatenate all of the [...CloseNotes] fields for a particular [fld_ActionPlanNumber], I believe you can forgo that query entirely.  The fConcatFld function does the aggregation for you, so you may be able to simply add the concatenation function to Query3.

What I'm not sure of is whether qry_CurrentActionPlans, qry_CurrentActionPlans_Description, qry_CurrentActionPlans_MaxTargetCloseDate, and qry_CurrentActionPlans_Notes play in this.  

If query #2 above is the "qry_CurrentActionPlans_Notes" query (and it appears to be), then you would need to drop that query it from the SQL below.

Try:

SELECT qry_CurrentActionPlans_MaxUpdateDate.MaxOffld_ActionPlanUpdateDate
     , tbl_ActionPlans.fld_ActionPlanNumber
     , qry_CurrentActionPlans_MaxUpdateDate.exp_AttestUpdateContact
     , tbl_ActionPlans.fld_ActionPlanTitle
     , qry_CurrentActionPlans_Description.exp_FullDescription
     , qry_CurrentActionPlans_MaxTargetCloseDate.MaxOffld_ActionPlanTargetCloseDate
     , qry_CurrentActionPlans_Notes.exp_FullNotes
     , tbl_ActionPlans.fld_ActionPlanActualCloseDate
     , tbl_ActionPlans.fld_IssueNumber
     , fConcatFld("qry_CurrentActionPlans_Notes_Detail","fld_ActionPlanNumber", "exp_UpdateDateNotes","string",[fld_ActionPlanNumber]) AS exp_FullNotes
FROM (((qry_CurrentActionPlans_MaxUpdateDate 
       RIGHT JOIN tbl_ActionPlans 
       ON qry_CurrentActionPlans_MaxUpdateDate.fld_ActionPlanNumber = tbl_ActionPlans.fld_ActionPlanNumber) 
       LEFT JOIN qry_CurrentActionPlans_Description 
       ON tbl_ActionPlans.fld_ActionPlanNumber = qry_CurrentActionPlans_Description.fld_ActionPlanNumber) 
       LEFT JOIN qry_CurrentActionPlans_MaxTargetCloseDate 
       ON tbl_ActionPlans.fld_ActionPlanNumber = qry_CurrentActionPlans_MaxTargetCloseDate.fld_ActionPlanNumber) 
       LEFT JOIN qry_CurrentActionPlans_Notes 
       ON tbl_ActionPlans.fld_ActionPlanNumber = qry_CurrentActionPlans_Notes.fld_ActionPlanNumber;

Select allOpen in new window

"The specified field '[fld_ActionPlanNumber]' could refer to more than one table listed in the FROM clause of your SQL statement".

I copy/pasted your SQL into a new query. Was that correct?
Error-Message.jpg

Yes, the problem now becomes that all of your queries contain that ActionPlanNumber field, so the fConcatFld function needs to know which table/query to take that field from.  My guess is that it should be the base table.  Try:

fConcatFld("qry_CurrentActionPlans_Notes_Detail","fld_ActionPlanNumber", "exp_UpdateDateNotes","string",[tbl_ActionPlans].[fld_ActionPlanNumber]) AS exp_FullNotes

Gotta run, it will be 3-4 hours before I can get back to my computer.

The new SQL works, and it produces the same results as my previous Query #3. However, that's the problem - it produces the same results, including the truncation.

Was the new SQL meant to resolve the truncation? Or was that simply an interim step?

I was hoping that would resolve the truncation issue.  Lets try something simple:

SELECT tbl_ActionPlans.fld_ActionPlanNumber
     , fConcatFld("qry_CurrentActionPlans_Notes_Detail","fld_ActionPlanNumber", "exp_UpdateDateNotes","string",[fld_ActionPlanNumber]) AS exp_FullNotes
FROM tbl_ActionPlans

Also, can you post the code you are using for the fConcatFld() function.  There are a variety of concatenation functions floating around, so it might be good to see if that is where your problem is.

The simple SQL statement above still truncates.

Here is the fConcatFld function:

Option Compare Database

'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Function fConcatFld(stTable As String, _
                    stForFld As String, _
                    stFldToConcat As String, _
                    stForFldType As String, _
                    vForFldVal As Variant) _
                    As String
'Returns mutiple field values for each unique value
'of another field in a single table
'in a semi-colon separated format.
'
'Usage Examples:
'   ?fConcatFld(("Customers","ContactTitle","CustomerID", _
'                "string","Owner")
'Where  Customers     = The parent Table
'       ContactTitle  = The field whose values to use for lookups
'       CustomerID    = Field name to concatenate
'       string        = DataType of ContactTitle field
'       Owner         = Value on which to return concatenated CustomerID
'
Dim lodb As Database, lors As Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"

    On Error GoTo Err_fConcatFld
    
    lovConcat = Null
    Set lodb = CurrentDb
    
    loSQL = "SELECT [" & stFldToConcat & "] FROM ["
    loSQL = loSQL & stTable & "] WHERE "
    
    Select Case stForFldType
        Case "String":
            loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
        Case "Long", "Integer", "Double":    'AutoNumber is Type Long
            loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
        Case Else
            GoTo Err_fConcatFld
    End Select
    
    Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
    
    'Are we sure that duplicates exist in stFldToConcat
    With lors
        If .RecordCount <> 0 Then
            'start concatenating records
            Do While Not .EOF
                lovConcat = lovConcat & lors(stFldToConcat) & Chr(13) & Chr(10) & Chr(13) & Chr(10)
                .MoveNext
            Loop
        Else
            GoTo Exit_fConcatFld
        End If
    End With
        
    'That's it... you should have a concatenated string now
    'Just Trim the trailing ;
    fConcatFld = Left(lovConcat, Len(lovConcat) - 2)


Exit_fConcatFld:
    Set lors = Nothing: Set lodb = Nothing
    Exit Function

Err_fConcatFld:
    MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
    Resume Exit_fConcatFld
End Function
'************ Code End **********

Select allOpen in new window

I'm thinking the problem may be in qry_CurrentActionPlans_Notes (which is what fConcatFld is trying to meld into a single record by ActionPlanNumber).  Was that the original query posted above, or is it something else?

OK,  Mine is slightly different, but what you have should work fine.

Have you tried calling fConcatFld from the immediate window with a specific value of the ActionPlanNumber that you know should return a very long string?

Is your ActionPlanNumber field actually a string?  Or is it more likely a "Long".  If it is an autonumber, replace "string" in your function call with "Long"

dale,
 regarding forcing the memo type take a look at allan brownes article about truncation memo and text fields. I am posting by cell phone right now and don't have the link handy but believe you need to address that in the first query before processing the concatenated data further in the outer query. Even if the first query is not in itself truncating the data.

The problem does start occurring in qry_CurrentActionPlans_Notes.  (I was mistaken about where the problem originates - see this previous comment.)

I'm not sure what you mean:

Have you tried calling fConcatFld from the immediate window with a specific value of the ActionPlanNumber that you know should return a very long string?

ActionPlanNumber is a string (data type is "Text") (e.g., "ADPA.12.01_001"), not a literal number.

songwritingcpa,

Is this resolved or did you inadvertently close the question? Did that base query help resolve it?

It worked!  Thank you!

@song,

Recommend you re-allocate those points.  The solution you accepted was basically the Sam as Miriam recommended in one of her initial posts.

To do this, click the "request attention" hyperlink in your original post and indicate you would like to reopen the question to rearward points.  Glad we were able to help.