Pulling all records in a query to one textbox

[AnitaPita]

Good morning,

I have a report with a textbox that has an expression in the control source, pulling from a query. I want it to pull all the records in the query into that one textbox (and have them in sort of a list, if possible). Instead what it is doing is pulling the first record, then putting the next record on a new page in the report - so I end up having one page for every record in the query. Sorry if this is a rookie question, I haven't had much experience using these textboxes in reports.

Thanks!!

 

[Mr. B]

You have the details section of your report way too tall.

Just for testing and to get an idea of how the report works, tyr moving all your controls in the details section up to the top of the details section. Then move the Page Footer section up as far as it will go. Now preview your report. You should now see the details of your report listed down the page.

You will need to do some formatting, but this should show you some things about designing reports.

 

[AnitaPita]

Thanks Mr. B,

I shortened the height of the Details section, but the text box is still only displaying the first record in the query. I've got 3 employee names in this particular query along with a reason they were out of the office. So when I pull all the records, I want it to display something like this:

John Doe - Vacation
Billy Somebody - Floating Holiday
Jill Unknown - Business

But instead, all I get is the first one only - John Doe - Vacation

Does that make sense?

Thanks for your help!

 

[Mr. B]

Not being able to see exactly how you have the report formatted, it is very difficult to say just what is wrong. If you have your textbox for the Name and the one for the Description at the top of the details section and the Page Footer section moved up immediately below the textboxes, then your report should show exactly what you are looking for.

Check to see that you have the "Force New Page" property on the Format tabl of the Property Sheet set to "None".

If you can post a copy of your database I am sure someone will be gald to take a quick look.

 

[AnitaPita]

After doing some research, I believe what I need to do is concatenate the multiple records in my query into one record....

 

[Mr. B]

Concatenating is normally only used for combining value from one field with the value from one or more other fields in the same record, not combining records.

You should not need to concatenate records. That is not your problem.

Did you try what I suggested?

 

[AnitaPita]

Hello again,

Yes I moved everything up and the next record did appear, but the problem is by moving the footer way up it messes with the formatting of my report....I have set this up to look like a calendar...So for instance, I have an August calendar and I have 2 records from my query that fall on August 1st so I want both those to appear in the appropriate box if that makes sense... thanks!

 

[Mr. B]

Ok, now I can understand what the situation is.

I have never tried to do what you are doing in a report. I have done what you are doing in a form, but not in a report.

I can tell you that what you are needing to do is use sub-reports, one for each day of the month and have each sub-report populate with the records for that one specific day. Yes, I am talking about having 35 sub-reports in your main report. This is because you actually must have a sub-report for each posible day of the month on which a day can occur. You will then need code in your report that will hide the un-needed sub-report.

Hope this points you in the right direction.

 

[AnitaPita]

Thank you! Right before I read your post I started thinking I could try a subreport, will probably just be a pain formatting everything...now I have hidden the header of this subreport but it still has a colored background? Any idea how I can get rid of the design in the back? I have tried to format the background as white/transparent etc....

 

[AnitaPita]

Got it!! Thanks for all your help!!