Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26

    Unanswered: SQL - Force a new line

    I have the following code in a VB script within an Access form. I’m updating the contents of a memo field with a combination of data contained in two other fields. After the update I can separate the two pieces of data with a space, & ‘ ‘ &, but I don’t know the correct code to force a line between them. Please advise. Thanks.

    strSQL = "UPDATE tblone, tbltwo SET tblone.MemoField = [tbltwo].[MemoField] & ' ' & [tbltwo].[Darren], " _

  2. #2
    Join Date
    Jan 2004
    Posts
    7
    windows uses a line feed and a carriage return for new lines.
    so use \n \r in your string to define a new-line in windows. see
    http://de2.php.net/manual/en/languag....syntax.double

  3. #3
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    Like this?

    strSQL = "UPDATE tblone, tbltwo SET tblone.MemoField = [tbltwo].[MemoField] & \n & [tbltwo].[Darren], " _

  4. #4
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    nope. that don't work....

  5. #5
    Join Date
    Jan 2004
    Posts
    7
    sorry,
    actually that was for php (here you have to use both : "\n \r")
    but in vbscript and all vb apps these are the characters 10 und 13 in ascii
    so it will be:

    "my string in the first line" & chr(10) & chr(13) & "my string in the second line"

  6. #6
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    The result in the updated field is:

    Text1 • • Text2

    but if i copy and paste this data from the Access table to a word doc it displays correctly:

    Text1
    Text2

    ?

  7. #7
    Join Date
    Jan 2004
    Posts
    7
    last idea:

    try only chr(10) or chr(13)

    "text" & chr(10) & " newline text"

  8. #8
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    same result, the space displays as a small square. I’ve searched and it appears to be the way access handles ASCII code, I haven’t been able to find a solution yet, thanks for your help..

  9. #9
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    Access can't print a newline character in the table display. Try it with a form pulling data from the table. It will display.

  10. #10
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    I gave that a whirl, the memofield in the form displayed the data the same, the two bits of data separated by two small squares.

  11. #11
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    What Access are you using?

  12. #12
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    I figured out your problem! Chr(10) is \n and Chr(13) is \r, so the order should be Chr(13) & Chr(10), not the other way around. Windows uses \r\n not \n\r!

  13. #13
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    doh and more doh! Thanks. I couldn’t see the woods for the trees. Cheers.

  14. #14
    Join Date
    Jan 2012
    Posts
    3

    best answer ever

    Quote Originally Posted by aus View Post
    I figured out your problem! Chr(10) is \n and Chr(13) is \r, so the order should be Chr(13) & Chr(10), not the other way around. Windows uses \r\n not \n\r!
    Has to be one of most helpful, concise responses I've read so far on all these boards. Helped me, too.

    thx

    ~blah

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •