Report code and dataset question

I have a strange (maybe not too strange) problem. I have data populating a report and everything is fine. This table also references another table where there might be zero to several prefixes associated with the name. If I have one row from the main table, but there are three prefixes that have to be concatenated to the beginning of the Name field (like a one to many situation), what would be the easiest way to do this?

I thought of doing a separate Report code and I have the following:

Function addPrefix(byval current as string, newToAdd as string) as string

dim deg as string

deg = current + " " + newToAdd

return deg

End Function

In the text box, I have it referencing it's own value. So the textboxes expression is

=code.addTitles(ReportItems.("textbox1").Value, Fields!NAM.Value)

I hope this makes some sort of sense. If the first record's value in the main table is THISNAME and it pulls the values VAL1 and VAL2 from the second dataset then I want the report to show VAL1 VAL2 THISNAME.

Thanks for the information

have you tried using sql-joins?
left/right-join should fulfill your needs if i understood your problem right|||

Yes, I have used joins. The record in the left table produces the desired results. However the first record pulls three records from the INNER JOIN. I need to attached those three records to the NAME field from the left table to that one record. So if the first record pulls 1, 2, and 3. I need the report to display as 1 2 3 NAME.

I was thinking of doing a for each statement in the code. Is there a way to pass the dataset to the code in a report?

|||

Why don't you sort it out in the select statement in the Dataset:

select min(p1.prefix)+min(p2.prefix)+min(p3.prefix)+p0.name

from name_file p0

inner join prefix_file p1 on p1.prefix > ' ' and p0.name_id = p1.name_id

inner join prefix_file p2 on p2.prefix > p1.prefix and p0.name_id = p2.name_id

inner join prefix_file p3 on p3.prefix > p2.prefix and p0.name_id = p3.name_id

It should give you the desired results.....

|||There can be zero or more prefixes. That is why I would like to pass the dataset onto the Report Code and then do a for each statement, however I don't think that is possible.