Results 1 to 3 of 3
  1. #1
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62

    Include Carriage Return in Query Results Returned to Excel

    Hi All,



    Is it possible to store a carriage return in a table and then retrieve it to Excel.

    This is the method I am using to call the query:

    Code:
    Set RecSet = Db.OpenRecordset(strSQL, dbOpenDynaset)
    Sheets(Sheets.Count).Cells(1, 4).CopyFromRecordset (RecSet)
    Right now, in the Access table, an example row looks like:

    Code:
    Service Charge $14.50 & Chr(13) & Chr(10) & SHIPPING CHARGE CORRECTION AUDIT FEE FEE BASED ON 11 PACKAGES AND $377.86 CORRECTION AMOUNT   $22.67
    When I copy the record set to Excel, the cell displays exactly as above and not what I would expect, which is:


    Code:
    Service Charge $14.50
    SHIPPING CHARGE CORRECTION AUDIT FEE FEE BASED ON 11 PACKAGES AND $377.86 CORRECTION AMOUNT   $22.67
    Thanks for the help!

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    have you tried just using chr(10) which I believe is all you need for a linefeed in excel

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I tested string concatenation with Chr(10) in query and converted to table. The line break won't show in Access field but when I copy/paste into Excel it did have break. However, Chr(13) & Chr(10) also pasted over. Export Wizard also exported both just fine.

    However, the Chr(13) & Chr(10) functions are not in the string, ASCII characters are. I doubt the functions are seen as more than just regular text within string. So why are the functions in the string and not the ASCII characters themselves?

    Apparently this is related to your other thread where you utilize Allen Browne's ConcatRelated function. You want a line break in place of comma as item separator. So where the procedure uses variable strSeparator, you could try vbCrLf. I know this makes the function less generic but I could not figure out how to pass these non-printing characters via function argument.
    Actually, can set the variable to vbCrLf:

    strSeparator = vbCrLf
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-18-2016, 10:07 AM
  2. Carriage return
    By devcon in forum Queries
    Replies: 2
    Last Post: 12-28-2012, 02:56 AM
  3. Replies: 10
    Last Post: 08-01-2012, 11:32 AM
  4. Carriage return in a CSV file
    By btidwell3 in forum Import/Export Data
    Replies: 1
    Last Post: 09-09-2011, 05:19 PM
  5. How to force carriage return between strings?
    By Divardo in forum Reports
    Replies: 1
    Last Post: 05-21-2009, 10:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums