Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Listing Records Horizontally, Rather Than Vertically 1

Status
Not open for further replies.

shortamericano

Programmer
Joined
Aug 7, 2007
Messages
10
Location
US
Hello Forum,

I've been searching for days about this one and have been coming up empty-handed. Perhaps it is because I don't know what key words to search for...

I have a report with a field (let's call it [FruitField])that returns records in the following manner:

Apple
Orange
Banana
Kiwi

I want the field to read horizontally, rather than vertically like:

Apple, Orange, Banana and Kiwi

Is there any way to do this on a report? I'm stumped! Thanks in advance to all who reply. This forum is excellent.

Thanks.
 
Have a look here:
faq701-4233

Or perhaps a Crosstab query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thanks PHV! I'm still new at coding so I'll have to review this. I'm not sure I understand how to integrate it into my report...
 
You would need to add a module to your mdb with the Concatenate() function in it. You can then use the function almost anywhere you would use other functions. For instance you could set a control source to:
=Concatenate("SELECT FruitField FROM tblNoName")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Thanks for your response Dhookom. Are you suggesting that I copy PHV's concatenate code into a new module and call it "Concatenate", then use the line: =Concatenate("SELECT FruitField FROM tblNoName")

Thanks.
 
Actually, it's my code. Copy the function to a new, blank module and save the module with a name like "modConcatenate". Don't give a module the same name as a function or sub.

When using the function, you will want to use your field and table names.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Very cool. I think I'm almost there. I've added the new module and added the line:

=Concatenate("SELECT FruitField FROM tblNoName")

with modifications for field and table names. How do I now group the concatenated values? In my original example, it would have been somethig like:

[Name] [FruitField]
Ben Apple
Ben Orange
Dan Banana
Ben Kiwi

Returning the following result:

Ben Apple, Orange, Kiwi
Dan Banana

Thank you for taking the time to respond dhookom!
 
You should really provide more information with your original posting rather than revealing a little at a time.

Assuming [Name] is a field in your report's record source:

=Concatenate("SELECT FruitField FROM tblNoName WHERE [Name] = """ & [Name] & """")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top