Listing Records Horizontally, Rather Than Vertically 1

[shortamericano]

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.

 

[PHV]

Have a look here:
faq701-4233

Or perhaps a Crosstab query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shortamericano]

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...

 

[dhookom]

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]

 

[shortamericano]

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.

 

[dhookom]

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]

 

[shortamericano]

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!

 

[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]