CHAR(13) in ADP Report Problem

[Datawonk]

Hi,

I am using a stored procedure as the source for a report in an Access
Project. This report displays letters (which are generated by the
database into a single TEXT field for each person, similar to a
mail-merge feature). The problem is that while CHAR(13) works as a
carriage return in SQL Server, it does not in Access (that uses
CHR(10)/(13)).

The code to generate the data in the stored procedure looks like this:

....
first + ' ' + last
+ CHAR(13) + address1
+ CHAR(13) + address2
....

and the physical data like this:

....
Joe Test
1000 Sample Drive
Suite 1100 A
....

However, when I view the report in the ADP, I have little rectangles
representing the CHAR(13) values, and all my text is run together. If
I try to use CHR(13)/(10) in my code, I get an "'CHR' is not a
recognized function name." error. If I put the CHR(10/13) in single
quotes it (obviously) treats it as standard text.

Any help with this problems would be greatly appreciated.

Thank you VERY much,
Birgit

 

[Robert Morley]

I could be wrong, but I believe SQL Server accepts *either* CHAR(13) or
CHAR(13) + CHAR(10) as a carriage return. Access only accepts 13/10 (aka
CR/LF). So if you replace all the CHAR(13)'s with CHAR(13) + CHAR(10), they
*should* both be happy.



Rob

 

[Datawonk]

Hi Robert,

Thanks for the prompt response. You are correct, Access will take the
13/10 ... however, I would like to "insert" that into the body of my
letter (the text field), instead of having to make an unbound text box
on my report and replacing the CHAR(13) with CHR(13) + CHR(10).

Any ideas how I can do that?

Thanks again,
Birgit

 

[Datawonk]

I just figured out my own question, if you use CHAR(13) + CHAR(10) in
your sql, Access will convert it correctly.

Thanks for the tip that I needed to use both.

Birgit

 

[Robert Morley]

Hehehe, that's what I'd meant...guess I just wasn't clear enough.


Rob