dump fields into a memo field

  • Thread starter Thread starter shank
  • Start date Start date
S

shank

I query a table and have these 2 fields: ItemNo and SongTitles. I need to
dump all the SongTitles into a memo field. How do I get these results....

ItemNo SongTitles
Disc1 1. TitleA
Disc1 2. TitleB
Disc1 3. TitleC
Disc1 4. TitleD
Disc1 5. TitleE
Disc1 6. TitleF
Disc1 7. TitleG
Disc1 8. TitleH
Disc1 9. TitleI
Disc2 1. TitleA
Disc2 2. TitleB
Disc2 3. TitleC
Disc2 4. TitleD
Disc2 5. TitleE
Disc2 6. TitleF
Disc2 7. TitleG
Disc2 8. TitleH
Disc2 9. TitleI

To look like this...

ItemNo SongTitles(memo field)
Disc1 1. TitleA
2. TitleB
3. TitleC
4. TitleD
5. TitleE
6. TitleF
7. TitleG
8. TitleH
9. TitleI
Disc2 1. TitleA
2. TitleB
3. TitleC
4. TitleD
5. TitleE
6. TitleF
7. TitleG
8. TitleH
9. TitleI
 
I'm using your query as a pattern and not having much your luck.
This is your query...
SELECT tblFamily.FamID, tblFamily.famLastName, Concatenate("SELECT FirstName
& Chr(9) & Role FROM tblFamMem WHERE FamID =" & [FamID],Chr(13) & Chr(10))
AS FirstNames
FROM tblFamily
WHERE (((tblFamily.FamID)=2));

This is my query...
SELECT [NEW ItemTitles].OrderNo, Concatenate("SELECT [NEW
ItemTitles]![SortKey] & [NEW ItemTitles]![Title] & [NEW ItemTitles]![Artist]
FROM [NEW ItemTitles] WHERE [NEW ItemTitles]![OrderNo] =" & [OrderNo]) AS
Titles
FROM [NEW ItemTitles]
WHERE ((([NEW ItemTitles].OrderNo)="2300"));

I get the following error...
Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters

I'm displaying 2 columns in the query: OrderNo and Titles. OrderNo
populates, but Titles doesn't. Can you see where I'm missing something?
thanks
 
If OrderNo is text, you must add single quotes around the field. Also, I
assume you want to place spaces between the fields and carriage return/line
feeds between records.

SELECT OrderNo,
Concatenate("SELECT [SortKey] & '. ' & [Title] & ' ' & [Artist]
FROM [NEW ItemTitles]
WHERE [OrderNo] ='" & [OrderNo] & "' ORDER BY SortKey",
Chr(13) & Chr(10) ) AS Titles
FROM [NEW ItemTitles]
WHERE OrderNo="2300";

This may create multiple records for each OrderNo so make the query into a
group by or use a record source for your main query that contains only
unique OrderNo values.

--
Duane Hookom
MS Access MVP


shank said:
I'm using your query as a pattern and not having much your luck.
This is your query...
SELECT tblFamily.FamID, tblFamily.famLastName, Concatenate("SELECT FirstName
& Chr(9) & Role FROM tblFamMem WHERE FamID =" & [FamID],Chr(13) & Chr(10))
AS FirstNames
FROM tblFamily
WHERE (((tblFamily.FamID)=2));

This is my query...
SELECT [NEW ItemTitles].OrderNo, Concatenate("SELECT [NEW
ItemTitles]![SortKey] & [NEW ItemTitles]![Title] & [NEW ItemTitles]![Artist]
FROM [NEW ItemTitles] WHERE [NEW ItemTitles]![OrderNo] =" & [OrderNo]) AS
Titles
FROM [NEW ItemTitles]
WHERE ((([NEW ItemTitles].OrderNo)="2300"));

I get the following error...
Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters

I'm displaying 2 columns in the query: OrderNo and Titles. OrderNo
populates, but Titles doesn't. Can you see where I'm missing something?
thanks
Click to expand...
 
So far, sorta good. I was using your DB for testing and building the query.
I've got it groomed the way I wanted. Thanks!

Problem: After everything worked in your DB, I opened my main DB and
imported the query and your module. Now I get the following error:

Compile error: User-defined type not defined

What else do I need to do? It works OK in your DB.
thank again!


Duane Hookom said:
If OrderNo is text, you must add single quotes around the field. Also, I
assume you want to place spaces between the fields and carriage return/line
feeds between records.

SELECT OrderNo,
Concatenate("SELECT [SortKey] & '. ' & [Title] & ' ' & [Artist]
FROM [NEW ItemTitles]
WHERE [OrderNo] ='" & [OrderNo] & "' ORDER BY SortKey",
Chr(13) & Chr(10) ) AS Titles
FROM [NEW ItemTitles]
WHERE OrderNo="2300";

This may create multiple records for each OrderNo so make the query into a
group by or use a record source for your main query that contains only
unique OrderNo values.

--
Duane Hookom
MS Access MVP


shank said:
I'm using your query as a pattern and not having much your luck.
This is your query...
SELECT tblFamily.FamID, tblFamily.famLastName, Concatenate("SELECT FirstName
& Chr(9) & Role FROM tblFamMem WHERE FamID =" & [FamID],Chr(13) & Chr(10))
AS FirstNames
FROM tblFamily
WHERE (((tblFamily.FamID)=2));

This is my query...
SELECT [NEW ItemTitles].OrderNo, Concatenate("SELECT [NEW
ItemTitles]![SortKey] & [NEW ItemTitles]![Title] & [NEW ItemTitles]![Artist]
FROM [NEW ItemTitles] WHERE [NEW ItemTitles]![OrderNo] =" & [OrderNo]) AS
Titles
FROM [NEW ItemTitles]
WHERE ((([NEW ItemTitles].OrderNo)="2300"));

I get the following error...
Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters

I'm displaying 2 columns in the query: OrderNo and Titles. OrderNo
populates, but Titles doesn't. Can you see where I'm missing something?
thanks


Duane Hookom said:
You can use the generic concatenate function as demo'd in the download at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP


I query a table and have these 2 fields: ItemNo and SongTitles. I
Click to expand...
need
to
dump all the SongTitles into a memo field. How do I get these results....

ItemNo SongTitles
Disc1 1. TitleA
Disc1 2. TitleB
Disc1 3. TitleC
Disc1 4. TitleD
Disc1 5. TitleE
Disc1 6. TitleF
Disc1 7. TitleG
Disc1 8. TitleH
Disc1 9. TitleI
Disc2 1. TitleA
Disc2 2. TitleB
Disc2 3. TitleC
Disc2 4. TitleD
Disc2 5. TitleE
Disc2 6. TitleF
Disc2 7. TitleG
Disc2 8. TitleH
Disc2 9. TitleI

To look like this...

ItemNo SongTitles(memo field)
Disc1 1. TitleA
2. TitleB
3. TitleC
4. TitleD
5. TitleE
6. TitleF
7. TitleG
8. TitleH
9. TitleI
Disc2 1. TitleA
2. TitleB
3. TitleC
4. TitleD
5. TitleE
6. TitleF
7. TitleG
8. TitleH
9. TitleI
Click to expand...
Click to expand...
Click to expand...
 
Most likely this is a references issue. Open any module and select
Tools|References and check to make sure none are marked missing.

--
Duane Hookom
MS Access MVP


shank said:
So far, sorta good. I was using your DB for testing and building the query.
I've got it groomed the way I wanted. Thanks!

Problem: After everything worked in your DB, I opened my main DB and
imported the query and your module. Now I get the following error:

Compile error: User-defined type not defined

What else do I need to do? It works OK in your DB.
thank again!


Duane Hookom said:
If OrderNo is text, you must add single quotes around the field. Also, I
assume you want to place spaces between the fields and carriage return/line
feeds between records.

SELECT OrderNo,
Concatenate("SELECT [SortKey] & '. ' & [Title] & ' ' & [Artist]
FROM [NEW ItemTitles]
WHERE [OrderNo] ='" & [OrderNo] & "' ORDER BY SortKey",
Chr(13) & Chr(10) ) AS Titles
FROM [NEW ItemTitles]
WHERE OrderNo="2300";

This may create multiple records for each OrderNo so make the query into a
group by or use a record source for your main query that contains only
unique OrderNo values.

--
Duane Hookom
MS Access MVP


shank said:
I'm using your query as a pattern and not having much your luck.
This is your query...
SELECT tblFamily.FamID, tblFamily.famLastName, Concatenate("SELECT FirstName
& Chr(9) & Role FROM tblFamMem WHERE FamID =" & [FamID],Chr(13) & Chr(10))
AS FirstNames
FROM tblFamily
WHERE (((tblFamily.FamID)=2));

This is my query...
SELECT [NEW ItemTitles].OrderNo, Concatenate("SELECT [NEW
ItemTitles]![SortKey] & [NEW ItemTitles]![Title] & [NEW ItemTitles]![Artist]
FROM [NEW ItemTitles] WHERE [NEW ItemTitles]![OrderNo] =" &
Click to expand...
Click to expand...
[OrderNo])
AS
Titles
FROM [NEW ItemTitles]
WHERE ((([NEW ItemTitles].OrderNo)="2300"));

I get the following error...
Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters

I'm displaying 2 columns in the query: OrderNo and Titles. OrderNo
populates, but Titles doesn't. Can you see where I'm missing something?
thanks


You can use the generic concatenate function as demo'd in the
Click to expand...
download
at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP


I query a table and have these 2 fields: ItemNo and SongTitles. I need
to
dump all the SongTitles into a memo field. How do I get these
results....

ItemNo SongTitles
Disc1 1. TitleA
Disc1 2. TitleB
Disc1 3. TitleC
Disc1 4. TitleD
Disc1 5. TitleE
Disc1 6. TitleF
Disc1 7. TitleG
Disc1 8. TitleH
Disc1 9. TitleI
Disc2 1. TitleA
Disc2 2. TitleB
Disc2 3. TitleC
Disc2 4. TitleD
Disc2 5. TitleE
Disc2 6. TitleF
Disc2 7. TitleG
Disc2 8. TitleH
Disc2 9. TitleI

To look like this...

ItemNo SongTitles(memo field)
Disc1 1. TitleA
2. TitleB
3. TitleC
4. TitleD
5. TitleE
6. TitleF
7. TitleG
8. TitleH
9. TitleI
Disc2 1. TitleA
2. TitleB
3. TitleC
4. TitleD
5. TitleE
6. TitleF
7. TitleG
8. TitleH
9. TitleI
Click to expand...
Click to expand...
Click to expand...
 
Back
Top