1

This issue sounds familiar, but please read it to the end, as it has a twist to similar questions I found in this forum or elsewhere. I am using Access 2010 to build a simple app to create some code to execute in another system. Part of it is to convert number of records per user into a single record, with all user entries separated by comma. To illustrate, here is the sample of my input:

USER    MBR
----    ---- 
USR1    DRG 
USR1    ABC 
USR1    XYZ 
USR2    123A 
USR2    ABS2

And I need it in this format:

USER   MBR_LIST
-----  ---------------
USR1   DRG, ABC, XYZ
USR2   123A, ABS2

So far, so familiar. I've used the code posted by Allen Brown back in 2008 (http://allenbrowne.com/func-concat.html), but I've run into an issue. The code works fine - when I debug it, I can see that my output string ("strOut") has all MBRs for a single user, as expected. Mind you, sometimes this string is over 7,000 char long. So, again following some great advice found around, I've created a table with the MBR_LIST field set to MEMO, and I execute Allen's function as:

INSERT INTO Table2 ( [USR], MBR_LIST )
SELECT B.USR, ConcatRelated("MBR","Table1","Table1.USR = '" & B.USR & "'","USR") AS Mbr_List
FROM (SELECT Table1.USR FROM Table1 GROUP BY Table1.USR)  AS B;

(NOTE: the query is built as such to avoid doing Group By on the Memo field, which is known to be truncating the Memo field)

Still, after doing this, my MBR_LIST field in the table shows only 320 chars (?).

I even tried adding an empty row, as suggested in this post: MS Access Create Table is Truncating Memo Field, but with no success - the field still gets truncated to 320 chars:

INSERT INTO Table2 ( [USR], MBR_LIST )
SELECT B.USR, "" as Mbr_List
FROM [Table2] as B Where (False)
UNION ALL
SELECT B.USR, ConcatRelated("MBR","Table1","Table1.USR = '" & B.USR & "'","USR") AS Mbr_List
FROM (SELECT Table1.USR FROM Table1 GROUP BY Table1.USR)  AS B;

As a last resort, using the MID() I've created 20 "chunks" of 300 chars each (Mbr_1 to Mbr_20) in my SELECT statement, and I got them all back fine. Then I wrapped this into another SELECT with those chunks concatenated (Mbr_1 & Mbr_2 & ... & Mbr_20) AS Mbr_LIST, and got - 320 chars!

Any ideas why and how to insert/display the full string created by the function?

Thanks, tribe

Community
  • 1
  • 1
tribe
  • 11
  • 1
  • Sorry, I don't know how to create table-looking code, so my sample is all messed up. hopefully you will understand my intent, or advise me how to code this properly in this forum. – tribe May 17 '17 at 14:48
  • 1
    Try letting it happen, then checking the data type of each field created, you may need to create the table the long way, but based on the input, or just adjust the column, then insert into. Can you open 2 recordsets, "select * " from both source and dest and check the field type of MBR_LIST please. Even try the select with the field rather than "" first. – Nathan_Sav May 17 '17 at 14:51
  • What happens if you omit the `INSERT INTO` part? Just execute the SELECT? – Andre May 17 '17 at 17:47
  • And try changing the FROM part to `FROM (SELECT DISTINCT Table1.USR FROM Table1) AS B`. Or simply `FROM Table1` - doesn't `ConcatRelated` return only one row per USR? – Andre May 17 '17 at 17:48
  • I don't have an Access SQL solution. I usually just deal with this type of issue on the back end (which in most of my projects is SQL server so I can CAST/CONVERT) or use VBA rather than SQL to work with long strings within Access. – SunKnight0 May 17 '17 at 18:07
  • Nathan_Sav, I will try your suggestion a bit later, as it looks asking for a bit of time. – tribe May 17 '17 at 19:43
  • Andre, the SQL also returns only 320 chars; I will try your other suggestion (DISTINCT) later tonight or tomorrow and let you know of the result. – tribe May 17 '17 at 19:44
  • Andre, I've actually tried your suggestion with the DISTINCT, and it gives me the same number of chars, 320. And ConcatRelated returns a single row for every USR in the passed record set - that is why I have to do a Group By (or DISTINCT) first. – tribe May 17 '17 at 20:02
  • Interesting. Can you upload a sample database with enough data to reproduce the issue to a file hoster? – Andre May 17 '17 at 23:20
  • Andre, how do I upload? I am new to Stack overflow (I can't even format the table ;) )... – tribe May 18 '17 at 15:16

1 Answers1

0

There is a workaround I created years ago using DLookup in a helper function.

Not the fastest, of course, but if anything else fails ...

Access Query truncates Memo Field

To insert a long text, use VBA when you have retrieved the values for USR and MBR_LIST:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Select [USR], MBR_LIST From Table2")

rs.AddNew
    rs!USR.Value = USR
    rs!MBR_LIST.Value = MBR_LIST
rs.Update
rs.Close

Set rs = Nothing
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • It seems to me your code serves to extract the full Memo field from the table, am I right? My problem is how to insert the full string (7000+ chars) INTO the Memo field in a table, so if I am right about your code, it might not be working for me. Anyway, thanks for it, I'm sure it will be useful later on. – tribe May 17 '17 at 19:49
  • This worked, thanks! Regrettably, in the end I need to append this output to a string and export to a text file - and then I guess that bug kicks in and creates some junk after 250+ chars... Bottom line is, it is taking too much time to do this in Access for what I need it for. Still, thank you all for help, its a great community! – tribe May 18 '17 at 16:39
  • Well, check it out. Don't assume too much without checking. – Gustav May 18 '17 at 19:05