Solved

Workaround for Concatation Expression that Truncates a Memo Field

Posted on 2013-05-10
27
339 Views
Last Modified: 2013-05-12
The following is truncating exp_UpdateDateNotes:

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)<>IsEmpty([tbl_ActionPlanUpdates]![fld_ActionPlanTargetCloseNotes])))
ORDER BY tbl_ActionPlanUpdates.fld_ActionPlanNumber, tbl_ActionPlanUpdates.fld_ActionPlanUpdateDate DESC;

fld_ActionPlanTargetCloseNotes is the memo field being truncated in exp_UpdateDateNotes.

How can I prevent this truncation?

(Please don't post any links to other workarounds. I'm an auditor, not a programmer. I pay for this service to have my solutions customized and hand-fed to me. I can do my own searches if I'm wanting general answers. I'm not trying to be difficult...just saying, for whichever solution I mark as being correct.)
0
Comment
Question by:songwritingcpa
  • 12
  • 10
  • 5
27 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39156055
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

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39156065
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.
0
 

Author Comment

by:songwritingcpa
ID: 39156083
mbizup,

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

Expert Comment

by:mbizup
ID: 39156094
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

Open in new window

0
 

Author Comment

by:songwritingcpa
ID: 39156104
fyed,

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

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

This second query is used in a third query:

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
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;

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

Expert Comment

by:Dale Fye (Access MVP)
ID: 39156114
@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.
0
 

Author Comment

by:songwritingcpa
ID: 39156118
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".
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39156123
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39156156
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

Open 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.
0
 

Author Comment

by:songwritingcpa
ID: 39156473
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39156489
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.
0
 

Author Comment

by:songwritingcpa
ID: 39156578
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39156615
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;

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:songwritingcpa
ID: 39156667
"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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39156692
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39156694
Gotta run, it will be 3-4 hours before I can get back to my computer.
0
 

Author Comment

by:songwritingcpa
ID: 39156719
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?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39156901
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.
0
 

Author Comment

by:songwritingcpa
ID: 39156924
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 **********

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39156957
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?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39156971
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"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39156987
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.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39157030
@miriam,

had not had a chance to look at that yet.

@song,  take a look at Allen Brown's article (I think this is the web page Miriam is referring to).  Start out with Miriams earlier recommendation of creating this as the base query:
SELECT fld_ActionPlanNumber
       , fld_ActionPlanTargetCloseNotes AS exp_UpdateDateNotes
       , fld_ActionPlanUpdateDate  
FROM tbl_ActionPlanUpdates WHERE (False)
UNION ALL
SELECT fld_ActionPlanNumber
       , "As of " & fld_ActionPlanUpdateDate & " >> " & fld_ActionPlanTargetCloseNotes 
       , tbl_ActionPlanUpdates.fld_ActionPlanUpdateDate 
FROM tbl_ActionPlanUpdates
WHERE tbl_ActionPlanUpdates.fld_ActionPlanTargetCloseNotes & "" <> ""
ORDER BY fld_ActionPlanNumber, fld_ActionPlanUpdateDate DESC

Open in new window


If you save this query, the field exp_UpdateDateNotes should now be a memo field, not a string.  Save that query and then try doing the fconcatfld( ) function call using that new query and field name.

I'm heading out to a fund raiser with my wife,  be back in 3 or 4 hours.
0
 

Author Comment

by:songwritingcpa
ID: 39157047
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39157511
songwritingcpa,

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

Author Comment

by:songwritingcpa
ID: 39157534
It worked!  Thank you!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39159594
@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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

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

8 Experts available now in Live!

Get 1:1 Help Now