469,326 Members | 1,616 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

SQL chr(10) & chr(13)

Hi,

I'm trying to concatenate address fields and insert a LF&CR after each line.
The SQL I use is based on

select
address1 & chr(10) & chr(13)&
address2 & chr(10) & chr(13)&
address3 & chr(10) & chr(13)&
address4 & chr(10) & chr(13)&
address5 & chr(10) & chr(13) &
postcode as Address
from addresses

However, although this works for some records, it doesn't for all of them!
Also on some records, only some of the newlines work, so I get results like:

1 A. Drive Someplace Somewhere
Somecounty
Somepostcode

(where I would like the first line split into 3)

It really does seem to work completely randomly!

Please help,
Chris
Nov 12 '05 #1
6 90084
> I'm trying to concatenate address fields and insert a LF&CR after each line.
The SQL I use is based on


The sequence is CR & LF, e.g. Chr(13) & Chr(10) or vbCrLf

HTH - Peter

--
No mails please.
Nov 12 '05 #2
I'm trying to concatenate address fields and insert a LF&CR after each line.
The SQL I use is based on


The sequence is CR & LF, e.g. Chr(13) & Chr(10) or vbCrLf


Of course, the intrinsic constants will not work in SQL, only in VBA, so one
must use the combination "Chr(13) & Chr(10)" in sql statements and in
form/report control sources.

--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)
NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<
Nov 12 '05 #3
"Peter Doering" <ne**@doering.org> wrote in message
news:bk************@ID-204768.news.uni-berlin.de...
I'm trying to concatenate address fields and insert a LF&CR after each line. The SQL I use is based on


The sequence is CR & LF, e.g. Chr(13) & Chr(10) or vbCrLf

HTH - Peter


Thanks, that sorted it..

I wonder why the other way worked sometimes but not others?

Chris
Nov 12 '05 #4
>>> I'm trying to concatenate address fields and insert a LF&CR after each line.
The SQL I use is based on
The sequence is CR & LF, e.g. Chr(13) & Chr(10) or vbCrLf

Of course, the intrinsic constants will not work in SQL, only in VBA, ...


Very true. Thanks, Bruce.

Peter

--
No mails please.
Nov 12 '05 #5
I wonder why the other way worked sometimes but not others?


You're likely referring to the MsgBox() function, where a simple Chr(13) will
give you the new line (in this case the "Chr(10)" is ignored). This is a unique
situation.

:-)
--
Bruce M. Thompson, Microsoft Access MVP
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)
NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<
Nov 12 '05 #6
IF the UN could work together so peacefully...hahaha
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by lauren quantrell | last post: by
reply views Thread by WebMatrix | last post: by
5 posts views Thread by Computer Guru | last post: by
17 posts views Thread by AWW | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.