Want text box to show in rows in report

[jukus]

Not sure if this is possible, but Just wandering if it can be done.

I have a report Movereport that has a sub report subreportTenants. The data are from tables that are related by movereportid. Well the subreport pulls names from the tenants table and can have up to 4 names with each movereport id.

My question is I have a text box in the subreport that the control source is set to =[FirstName]&" "&[LastName] which does what I want. The question is each name then goes in the report in a column. i.e.

Gene Johnson

Stephanie Johnson

etc.

Is there a way to get the name to diplay all on the same row of the report. i.e.

Gene Johnson Stephanie Johnson etc.

Any help in this or letting me know it can not be done would be great.

I can live with the way it does it now, it just takes up a lot of room on the report and it would work better in a row.

 

[pbaldy]

This type of thing?

http://www.mvps.org/access/modules/mdl0004.htm

 

[jukus]

I have tried several different ways to get the function to work, but each time I get a syntax error. It may be what I am after but not sure since I can not seem to get it to work.

 

[CyberLynx]

What is the Error and on what line?

In any case...if you want multiple columns within your report then place the Report in Design view and select then menu items File then Page Setup. When in the Page Setup dialog window select the Columns tab.

Enter the Number of Columns you want and the Row Spacing. You can then select which Column Layout you would like to use, Down then Across or Across then Down.

.

 

[pbaldy]

Can't fix a syntax error without seeing the code you're using. Columns would be another way to display the data, presuming there isn't much else with it.

 

[jukus]

Since there was only one item in the subreport the across then down worked and produced the results I was wanting.

I copied and pasted the actual function to a module and when I tried to run the query I get a syntax error when calling the function, saying invalid or missing item.

here is the way my sql for the query looks.

SELECT tblMoveReportInfo.*, fConcatChild("tblTenants","MoveReportID","LastName","Long",[MoveReportID]) AS SubFormValuesFROM tblMoveReportInfo

The syntax error always higlighted the last tblMoveReportInfo.

Thanks

 

[Brianwarnock]

Is tha a copy and paste of your SQL, there is no ; and "lastname " appears to have a space.

Brian

 

[pbaldy]

A syntax error would be caused by not having a space before the word "FROM",

 

[Brianwarnock]

I wondered about that Paul , but as the example in the Link didn't I assumed that it was something unique to this method.

Brian

 

[pbaldy]

Sorry Brian, your post wasn't there when I started typing (slow fingers today). I was talking about this, which is not an issue with the function but in the SQL itself:

..SubFormValuesFROM...

By the way, while the QBE always adds the semi-colon to terminate the SQL statement, it isn't really required. I never add them when creating SQL in VBA. I didn't even notice the space after LastName, which may also be a problem. Good eye!

 

[Brianwarnock]

It was in the example of how to use it with the function code, mind you I would have tried it with a space if I had the db. Didn't realise the ; was optional.

Brian

 

[pbaldy]

I never noticed that.

It's definitely a typo in the link, as you'd have to have a space before the keyword "FROM". I'll report it to the mvps site.