By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 913 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

SQL chr(10) & chr(13)

P: n/a
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
Share this Question
Share on Google+
6 Replies


P: n/a
> 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

P: n/a
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

P: n/a
"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

P: n/a
>>> 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

P: n/a
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

P: n/a
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.