SQL chr(10) & chr(13)

[C L Humphreys]

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

[Peter Doering]

> 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.

[Bruce M. Thompson]

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.<<

[C L Humphreys]

"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

[Peter Doering]

>>> 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.

[Bruce M. Thompson]

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.<<

[hal boyles]

IF the UN could work together so peacefully...hahaha