Create report that displays Oracle CLOB data correctly?

[egarobar]

I am using Access 2003 (on WinXP) to read from an Oracle db, where there is a table with a CLOB which is a variable-size text field. In the 'linked table' which is created in the Tables panel of Access, the CLOB becomes a Memo field. I need to create a report that displays the data from this CLOB in the correct format. The data in the CLOB contains any number of chr(13), i.e. Carriage Returns, without LF, to separate lines of data that are entered in the Oracle application.

The data that comes out in the report looks like this:
11//04/06 First letter sent18/04/06 Student disputing debt04/05/06 e-mail sent ....
because the chr(13)s are ignored in the output.

I need it to look like this:
11//04/06 First letter sent
18/04/06 Student disputing debt
04/05/06 e-mail sent
....

I have tried creating a copy table (both in Oracle and in Access), holding a copy of the data after replacing every chr(13) with chr(10), and I have also tried replacing chr(13) with chr(13)&chr(10). These either give the same result or cause the data to be truncated after the first chr(13), so that's no good.

I found a reference to the Split function in Access Help, but I don't know enough about writing Access code to know how to use this in the Modules area. I tried putting "split(fieldname,chr(13),1)" in the control source on the report, but Access doesn't recognise this; it treats the 'split..' expression as a parameter. The idea of creating an array sounds like a good one, but then I need to know how to get the array into the report; the field is set up as a text box, and I don't see a way of converting it to an array - you can change it to a list box, but that doesn't seem to help.

Can anyone help with this? Maybe you could paste me some code that gives me an idea of how to create an array from a field, and then describe how to get this into a report field. I am used to using Access to write pass-through queries to get reports from Oracle, but I am not very clued up about Access code, so please don't assume too much!

Many thanks in advance.

[Rabbit]

I don't know a whole lot about this but it looks like Access is stripping the chr(13) when it imports. So what you'll have to do is replace all the chr(13) with some sort of escape character, import that, and then put the chr(13) back in.

[egarobar]

Hi rabbit

Actually when Access imports it, the chr(13) is still there - if I copy the whole table into an Access table, I can see the chr(13) using select mid(...).

The problem is getting it to print right in the report.

Any more clues?
Cheers

[NeoPa]

I know that using Memo fields can often cause problems, but I wasn't able to produce your problem with a simple TextBox on a report bound to a simple Memo field in my database.
I suspect it's related to Memo fields somehow, but I just haven't found the right situation.