Troubleshooting: QB Multi-Line description fields are not displayed properly in MS Access reports

Problem Description 1

     I have found that the text I received from QODBC for multi-line fields (e.g., the item purchase or sales description) uses only 0x0A line feeds rather than 0x0D. Carriage Return & 0x0A Linefeed pairs. As a result, these multiline values are not displayed correctly in MS Access.

     I have checked QB by selecting the text in such a multiline field and pasting it into a hex editor, and there it has the preferred 0x0D and 0x0A pairs, but I do not know if QB stores the underlying data like that. It would appear that QODBC presents the data like this.

     Does anyone know if there are any settings in either QuickBooks, QODBC, or MS Access that allow me to configure things so that my MS Access reports display correctly and easily?

Solutions 1

Report Displaying

     I am kind of inclined to have you check what was "embedded" in your long description text in the first place. I just ran up a report based on the data I tested with the forum post: Incomplete Data returned, and everything appeared as expected for me:

Pass-Through Query

     I used this pass-through query for my report:

SELECT SalesOrderLine.TxnID, SalesOrderLine.TxnNumber, SalesOrderLine.CustomerRefListID, SalesOrderLine.CustomerRefFullName, SalesOrderLine.SalesOrderLineItemRefFullName, SalesOrderLine.SalesOrderLineDesc
FROM SalesOrderLine SalesOrderLine

Created Form

     When using an MS Access pass-through query, a form created using the pass-through query also displays the very long Service Item correctly... but it doesn't show the embedded carriage return/line feed seen in the report above.

     Most of us prefer our long text to word wrap the container or column width that we display the descriptions in.

Problem Description 2

     My description text isn't long so much, but it has multiple lines to help with purchasing. For example,

"Lock & Key Set Comprising:
1x 70-108LG - Abloy Kit CL108N-LH KD
2x 70-PROTEC - Abloy Disklock Pro Cut Keys
Numbered sequentially from last order."

     I would like to be able to show this text as per above, but with my MS Access report it shows as follows:

"Lock & Key Set Comprising:_1x 70-108LG - Abloy Kit CL108N-LH KD_2x 70-PROTEC - Abloy Disklock Pro Cut Keys_Numbered sequentially from last order." (where the '_' are shown as a square.)

     Your example appears to suffer the same problem in that the CR is lost in QOBDC.

Possible Solution 2

VB Replace Function

     I have fixed the problem by calling the VB to replace the function within the SQL statement, as shown below.

DoCmd.RunSQL _
"INSERT INTO Local_ItemInventory ( Part, Description, QuantityOnHand ) " & _
"SELECT Name, replace(PurchaseDesc,chr(10),chr(13) & chr(10)), QuantityOnHand " & _
"FROM ItemInventory " & _
"WHERE IsActive=Yes"

REPLACE Function Supported by QODBC

     Just so other developers know, QODBC does support the REPLACE function directly too:

{fn replace(sql_string_to_search, sql_string_to_find, sql_string_to_replace_with)}

     For example:

SELECT Name, {fn REPLACE("Name", 'i', 'xxxxxx')} AS "Replaced" FROM Customer