Results 1 to 13 of 13
  1. #1
    CP611 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55

    How to enter onto a different Line in a query

    Hello,

    My current query is showing as ADDRESSLINE1: StrConv([Address],3) & "|" & StrConv([Address2],3) to incorporate 2 address lines into 2 with a | in between and correctly formatted. However, I need Address2 to be moved onto the new line so when a spreadsheet is outputted it looks like the below for example, but within one cell:

    6 The Station
    London

    I have tried putting Ctrl + Enter in between the " " instead of a | but that doesn't seem to work.



    Any help

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try

    StrConv([Address],3) & Chr(13) & Chr(10) & StrConv([Address2],3)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Chr(13) & Chr(10) is also vbCRLF

  4. #4
    CP611 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    I have tried Chr(13) & Chr(10) but that doesn't work, it just comes out like 6 The StationLondon. I tried vbCRLF instead but when I went to convert it it told me to put in a parameter value for vbCRLF?

    Thanks!

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Just tested and it works as expected. You won't see the second line unless you drag the row height bar down. I assume it would show in Excel when exported.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    CP611 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    thank you this works now

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! FYI, vbCrLf would only work in VBA, which is why you got a parameter prompt trying to use it in a query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    CP611 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    Hi there,

    I used the above query which works fine. However, if there is nothing in address2 then it still has some hidden data (presumably like a Ctrl J) at the end of the line. Therefore, when importing into a CRM it has an extra space between address and then the postcode which is underneath. Is there a way to edit this code so that if address2 is blank then nothing is entered at the end when exporting into excel? Hope that makes sense!

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If it's Null this may work:

    StrConv([Address],3) & (Chr(13) + Chr(10) + StrConv([Address2],3))

    The + propagates Null.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    CP611 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    Hi there,

    This still leaves the extra space when imported into our CRM. However, if we were to type the address out manually then import it then there isn't the extra gap. Any ideas?

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What's the complete expression now?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    CP611 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    55
    ADDRESSLINE1: StrConv([Address],3) & (Chr(13) + Chr(10)+ StrConv([Address2],3))

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The first address doesn't have a trailing space, does it? Can you tell if the second address is Null or a zero length string? What I gave should work if it's Null, not if it's a zero length string. I suppose you can try:

    StrConv([Address],3) & IIf(Len(Nz([Address2], "")) = 0, "", Chr(13) & Chr(10) & StrConv([Address2],3))

    If I haven't mucked up the parentheses.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 5
    Last Post: 01-24-2018, 10:45 AM
  2. Replies: 4
    Last Post: 12-23-2017, 11:25 AM
  3. Replies: 1
    Last Post: 10-03-2014, 06:06 AM
  4. Replies: 4
    Last Post: 03-11-2014, 06:38 PM
  5. Field auto enter new line on enter event
    By Ruegen in forum Forms
    Replies: 3
    Last Post: 09-17-2013, 09:00 PM

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