Good Morning,

I have been asked to generate a summary report, that can be exported to excel. As far as generate this repot, I have no problems doing it. For this problem the report is contained on 3 tables.

A primary table
A junction table
A list table

For a normal report, I would put the junction table in a subreport and have it it come out nice and clean. However, when I export a report with a subreport into excel the data goes funky. What I am wanting to do is instead of a subreport put all the data into a single text box on the report.

The basic layout is.

I have a house, which is one entry, and the house has 5 different areas that needs repaired, 5 entries in the junction table.

On the report I want the one house to show up, and on that line those 5 areas need to be populated into a single text box. I can write a query that gives those 5 areas, however it also gives 5 records of the same house. I've worked on it for several days now and cannot get it run properly.

Thanks for your time, thoughts and knowledge on this issue!

if you can marshall the data in a query (or series of interlinked / dependant queries, tjhen why not just run the queries in excel, rather than go via a report. or write the excell file from within Access VBA

if you want to do the latter than there's some stuff in the code bank from PootleFlump and others that should help

From what I had read on the web, writing the excel file was highly discouraged so I didn't attempt to go down that path.

As far as exporting the queries, semi-laziness. These individuals are wanting this report in a very predetermined format. I can make the exact report they want on access, look the exat same as when they print it out form excel. However, they want this report in excel.

What I am trying to do, to save myself time, is create the report is access and just export it to excel instead of having to build the excel file everytime. This is the first time I have run accross needing to do this. If there is a different way to approach this please let me know, this was just the first way that made since in my mind.

Everything on my export works perfectly outside of this junction table data. When I use a subform, it puts each data set in its own excel row which messes up the format of the report.

If it was up to me, I'd just use the access report, but alas I must make my customers happy

I have taken healdem's advice and created an export class for the information. However, my next take back is with rich text formatting. There are a couple of fields in the database that have rich text formatting. For this specific report the formatting is not an issue, is there a good way to remove the RTF from this data.

I know that if I put the data into a textbox and take it form the textbox instead of the query it will cross over fine, didn't know if there was a way to do it through VBA without having to link it to a textbox.

Thanks for the time, thoughts and knowledge!