[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 are not displayed properly in MS Access reportsProblem Description 1I 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 1Report 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: 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 2My description text isn't long so much, but it has multiple lines to help with purchasing. For example, "Lock & Key Set Comprising: 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 2VB Replace Function I have fixed the problem by calling the VB to replace the function within the SQL statement, as shown below. DoCmd.RunSQL _ 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
| |
|
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.