The poster was trying to build a Text file from the table records for an external application. His requirement was to combine all the field values and all the records into one long text data. Naturally, when someone asks to combine all table records, I typically recommend this SimpleCSV() function. Unfortunately, the function did not work for him as it produced weird characters in the output string. See image below...
I have never run into this issue before, so I started doing some research. It turns out, when you use a Recordset with a calculated column where multiple fields are concatenated to combine the data, the calculated column only looks normal if the resulting value is less than 255 characters.
To demonstrate this issue, I created a demo file, which you can download from my website.
In the demo, I created a table with two Text fields, both containing exactly 255 characters. When we use a Recordset to pull just Field1, the result looks okay. But when we pull both Field1 and Field2 into one column in a Recordset, then we run into this issue. Interestingly enough, I also found out that using DLookup() does not produce a problem.
I brought this issue to Microsoft's attention, and it was confirmed by the Access Team. They also promised to work on a fix for future release. In the meantime, if you must combine multiple fields using a Recordset, I recommend pulling all the fields individually when you create the Recordset and then just loop through the fields and concatenate them from within your procedure.
I hope you find this article helpful. As always, any comment is welcome.
Thank you very much for reading.
No comments:
Post a Comment