Report details on one line

[Shimon]

Hi All,
This is my first post in this group.
For each record i have a field which is City of origin.
I would like an index grouped by City and the details (which is item
number stored in another field) listed consecutively on one (or more)row.
I would like a comma separating each number.

It should look something like this.

Albany: 45, 68, 85, 99

Spring Valley: 105, 334, 55

Thanks for any help,
Shimon

 

[KARL DEWEY]

Use Duane Hookom's generic Concatenate() function. The code is at

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[Marshall Barton]

For each record i have a field which is City of origin.
I would like an index grouped by City and the details (which is item
number stored in another field) listed consecutively on one (or more)row.
I would like a comma separating each number.

It should look something like this.

Albany: 45, 68, 85, 99

Spring Valley: 105, 334, 55

You need to create a function to do that. There's a popular
one at:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

 

[Shimon]

Thanks Karl,
I'll check it out.
Shimon

 

[Shimon]

Thanks Marsh.
Have a great year (it's the Jewish New Year next week)
Shimon

 

[Shimon]

Hi,
I downloaded it and it works fine in the sample queries, but when I use
it in my query I get an runtime error 3061 too few parameters, expected
1. at this line.

Set rs = db.OpenRecordset(pstrSQL)


This is the pstrSQL string in the watch window.

"SELECT Item_Number FROM Item_List WHERE City = London"

Item_List is the name of the table.

Thanks fo any help,
Shimon

 

[John Spencer]

pstrSQL should read as follows. Text values must be delimited with ' or ".
"SELECT Item_Number FROM Item_List WHERE City = 'London'"

Your string
"SELECT Item_Number FROM Item_List WHERE City = London"
meant that the query was looking for a field or parameter with the name London

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Shimon]

Hi John,
Thanks for responding. We had the New Year holiday, so I did not get to
checking before today.

Here is the strange part. When I run the queries that came in example DB
from Duane Hookom, the string seems very much the same as mine.
Here are the SQL statements and string values of both.

SELECT tblFamily.FamID, tblFamily.famLastName, Concatenate("SELECT
FirstName FROM tblFamMem WHERE FamID =" & [FamID]) AS FirstNames
FROM tblFamily;

"SELECT FirstName FROM tblFamMem WHERE FamID =1"

SELECT DISTINCT Item_List.City AS Cities, Concatenate("SELECT
Item_Number FROM Item_List WHERE City =" & [City]) AS Items
FROM Item_List
WHERE (((Item_List.City)="London"));

"SELECT Item_Number FROM Item_List WHERE City =London"



If anybody can figure out how to correct the problem, I would appreciate
it.
Thanks,
Shimon

 

[Shimon]

Got it.
I thought I tried this before I posted this last post, but I guess I
didn't get it right then.
In Duane's example, the field is a number but in my case it is a text,
so I had to add Chr(34) like this,

Items: Concatenate("SELECT Item_Number FROM qselCurrentAuctionItems
WHERE Place_Of_Printing =" & Chr(34) & [Place_Of_Printing] & Chr(34)).

I changed the source, as the item numbers did not come out in ascending
order, so I use a query which does have them in ascending Item_Number.

Is there any additional way to add quotes in a query? &' &
[Place_Of_Printing]? I tried many ways and was not successful.

Thanks to all for pointing me in the right direction.
Shimon

Hi John,
Thanks for responding. We had the New Year holiday, so I did not get to
checking before today.

Here is the strange part. When I run the queries that came in example DB
from Duane Hookom, the string seems very much the same as mine.
Here are the SQL statements and string values of both.

SELECT tblFamily.FamID, tblFamily.famLastName, Concatenate("SELECT
FirstName FROM tblFamMem WHERE FamID =" & [FamID]) AS FirstNames
FROM tblFamily;

"SELECT FirstName FROM tblFamMem WHERE FamID =1"

SELECT DISTINCT Item_List.City AS Cities, Concatenate("SELECT
Item_Number FROM Item_List WHERE City =" & [City]) AS Items
FROM Item_List
WHERE (((Item_List.City)="London"));

"SELECT Item_Number FROM Item_List WHERE City =London"



If anybody can figure out how to correct the problem, I would appreciate
it.
Thanks,
Shimon

pstrSQL should read as follows. Text values must be delimited with '
or ".
"SELECT Item_Number FROM Item_List WHERE City = 'London'"

Your string
"SELECT Item_Number FROM Item_List WHERE City = London"
meant that the query was looking for a field or parameter with the
name London

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

Other ways is to use single quotes (apostrophe)

"SELECT Item_Number FROM qselCurrentAuctionItems WHERE Place_Of_Printing ='"
& [Place_Of_Printing] & "'")

Or to use two double quotes inside the quotes.

"SELECT Item_Number FROM qselCurrentAuctionItems WHERE Place_Of_Printing ="""
& [Place_Of_Printing] & """")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County