[QODBC-Desktop] QB Multi-Line description fields not displayed properly in MS Access reports
Posted by brad waddell on 12 March 2009 05:21 PM

Troubleshooting: QB Multi-Line description fields 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 linefeeds 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 into a hex editor and there it has the preferred 0x0D,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 any settings in either QuickBooks, QODBC or MS Access that allows me to easily configure things so that my MS Access reports display correctly?

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 see 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 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 replace 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 Supportted 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

(166 vote(s))
Not helpful

Comments (1)
Mark Loevenstein
14 June 2018 01:33 PM
I'm trying to compare the Notes column in the TimeTracking table between a SQL database and QODBC. How can I compare the two when the carriage returns are handled differently between both?

In VB Demo, I can only see the first line of the Notes column when I query it even when there are more lines that were written after the "Enter" button was pressed.

I'd like to use the {fn REPLACE("Name", 'i', 'xxxxxx')} function you made for QODBC and do a similar reformat of the SQL Notes column in order to compare the two, but I want to replace the unique spacing like carriage returns with regular spaces, which doesn't seem to be possible in QODBC from what I can find.
Post a new comment
Full Name:
CAPTCHA Verification 
Please complete the captcha below (we use this to prevent automated submissions).