Carriage Return Problems With SOAP and Access Reports

[Guest]

We are moving from 2 tier (ASP.Net to SQL 2005) to 3 tier (ASP.Net to Web
Service using SOAP to SQL 2005) with MS Access 2003 as the reporting engine.
It turns out SOAP changes the vbCrLf so the Access reporting engine will not
recognize it properly and display multiple lines. The result is a bullet
style dot used for an unrecognized character.

in the debugger, when we display the field value in the Immediate window,
the line returns are displayed fine but the final report rendering is not
accurate. The character value is chr(10) which is usually what I use to
insert a carriage return. I have even tried replacing chr(10) with chr(10)
in the reports input query thinking it is partially accurate and the replace
will override it but to no avail.

Data is properly displayed with the 2-tier (non-SOAP) processed information,
but not with the SOAP processed data.

In Studio.Net, if I use Crystal against the data it is properly displayed.
I have quite a few reports in MS Access and do not want to migrate to Crystal
if I don't have to.

The public blogs suggest I change my SOAP connections to Base64, but that
has consequences in other areas which are worse.

What can I do with MS Access to correct this issue?

 

[Guest]

After reviewing other newsgroups and BLOGS, I was certain the Access solution
would not be simple. After submitting this question, I reviewed other
Carriage Return entries in this newsgroup and noticed something I had not
seen before. I applied what I saw and corrected my issue. The solution is
not pretty, but works. On all fields which can include carriage returns,
here is what I have to have in the query:

ReportFieldName:
IIf(InStr(1,[tblFieldName],Chr(13))>0,[tblFieldName],Replace([tblFieldName],Chr(10),Chr(13) & Chr(10)))

It appears a true reporting carriage return must have both 13 + 10 whereas
SOAP only provides 10. With the above statement, if the line has 13 in it,
leave it alone; otherwise check for all 10's and add a 13. This allows for
backwards compatibility.