I have found that the text I received from QODBC for multi line fields (eg the item purchase or sales description) uses only 0x0A linefeeds rather than 0x0D Cariage 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 prefered 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? I'd rather not have to do this programatically.

Thanks,

Vincent.

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

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

When using a 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 I 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.

Thanks Tom,

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

"Lock & Key Set Comprising of:
1x  70-108LG - Abloy Kit CL108N-LH KD
2x  70-PROTEC - Abloy Disklock Pro Cut Keys
Numbered sequenctially 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

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

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

However, a quick check with one of my colleagues set me onto an idea and 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"

Maybe someonelse will find this resolution usefull.

Cheers

Vincent

Very nice, 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