append memo fields together sequentially

A

AlanO

Hi, I'm new here and I hope you can help me. I was given this data and have
no control as to how it was/is created.

I have a table that has an ID, SEQ_NUM, and a MEMO field. The data in the
memo field is a document. Each memo field is 2040 characters long and the
next memo field in the squence is the next part of the document. I am trying
to append all of the memo field together in sequential order to create one
memo field that will conain the complete document in order. I eventually have
to limit the size of each document to 60,000 charcters. I hope this makes
sense.

ID SEQ_NUM MEMO

1 1 PAGE 1
1 2 PAGE 2
1 3 PAGE 3
1 4 PAGE 4
2 1 PAGE 1
2 2 PAGE 2
 
J

John Spencer

You can use Duane Hookom's Concatenate code to combine this items into one

SELECT ID,
CONCATENATE("SELECT [Memo] FROM [YourTable] WHERE ID =" & ID & " ORDER
BY SEQ_NUM",Chr(13) & Chr(10))
FROM [YourTable]
WHERE SEQ_Num = 1


That assumes that every ID has a Seq_num equal to 1.

Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with
both ADO and DAO. There are comments regarding which lines to comment or
uncomment based on which library you prefer. Access 97 is mostly DAO
while the default for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane for a
database with the concatenate function in it. You can copy the function
and modify it if needed to run on your computer

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
A

AlanO

Thanks John-

I am still having issues. When I used the code I received the error,
undefined function 'concatenate' in expression. I have changed the field name
to match the one in my table. The CSITE_SEQ(seq_num) ranges in value from 1 -
13009556

SELECT SITE_ID,
CONCATENATE("SELECT [textfield] FROM [test] WHERE SITE_ID =" & SITE_ID & "
ORDER
BY CSITE_SEQ",Chr(13) & Chr(10))
FROM [test]
WHERE CSITE_SEQ = 1

John said:
You can use Duane Hookom's Concatenate code to combine this items into one

SELECT ID,
CONCATENATE("SELECT [Memo] FROM [YourTable] WHERE ID =" & ID & " ORDER
BY SEQ_NUM",Chr(13) & Chr(10))
FROM [YourTable]
WHERE SEQ_Num = 1

That assumes that every ID has a Seq_num equal to 1.

Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with
both ADO and DAO. There are comments regarding which lines to comment or
uncomment based on which library you prefer. Access 97 is mostly DAO
while the default for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane for a
database with the concatenate function in it. You can copy the function
and modify it if needed to run on your computer
Hi, I'm new here and I hope you can help me. I was given this data and have
no control as to how it was/is created.
Click to expand...
[quoted text clipped - 15 lines]
2 1 PAGE 1
2 2 PAGE 2
Click to expand...
Click to expand...
 
J

John Spencer

Did you go to the site and download the database and then extract the function
and paste it into a module in your database. If so, did you save the module
with a name OTHER than Concatenate. A module should never have the same name
as a function.

Here is the code for the function. Copy it and paste it into a vba module.

'================= Begin Function ======================
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
' Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines========
'====== comment out ADO below ========
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Set db = CurrentDb
Set db = DbEngine(0)(0) '<<<< Faster to create >>>>
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines========
'====== comment out DAO above ========
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing

'====== comment out next line for ADO ===========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

'================= END Function ======================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks John-

I am still having issues. When I used the code I received the error,
undefined function 'concatenate' in expression. I have changed the field name
to match the one in my table. The CSITE_SEQ(seq_num) ranges in value from 1 -
13009556

SELECT SITE_ID,
CONCATENATE("SELECT [textfield] FROM [test] WHERE SITE_ID =" & SITE_ID & "
ORDER
BY CSITE_SEQ",Chr(13) & Chr(10))
FROM [test]
WHERE CSITE_SEQ = 1

John said:
You can use Duane Hookom's Concatenate code to combine this items into one

SELECT ID,
CONCATENATE("SELECT [Memo] FROM [YourTable] WHERE ID =" & ID & " ORDER
BY SEQ_NUM",Chr(13) & Chr(10))
FROM [YourTable]
WHERE SEQ_Num = 1

That assumes that every ID has a Seq_num equal to 1.

Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with
both ADO and DAO. There are comments regarding which lines to comment or
uncomment based on which library you prefer. Access 97 is mostly DAO
while the default for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane for a
database with the concatenate function in it. You can copy the function
and modify it if needed to run on your computer
Hi, I'm new here and I hope you can help me. I was given this data and have
no control as to how it was/is created.
Click to expand...
[quoted text clipped - 15 lines]
2 1 PAGE 1
2 2 PAGE 2
Click to expand...
Click to expand...
Click to expand...
 
A

AlanO

thanks agian. I didn't realize I had to download the module. I downloaded and
imported the module into my database. I am now getting the error compile
error user-defined type not defined. here is what is highlighted when I get
this message

rs As New ADODB.Recordset

This is my first experience with modules so bear with me please.



John said:
Did you go to the site and download the database and then extract the function
and paste it into a module in your database. If so, did you save the module
with a name OTHER than Concatenate. A module should never have the same name
as a function.

Here is the code for the function. Copy it and paste it into a vba module.

'================= Begin Function ======================
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
' Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines========
'====== comment out ADO below ========
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Set db = CurrentDb
Set db = DbEngine(0)(0) '<<<< Faster to create >>>>
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines========
'====== comment out DAO above ========
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing

'====== comment out next line for ADO ===========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

'================= END Function ======================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.
Thanks John-
Click to expand...
[quoted text clipped - 37 lines]
Click to expand...
 
J

John Spencer

Probably you will need to comment out that line and the next line
Just type an apostrophe at the beginning of the lines

On four lines above that you will need to remove the apostrophe so that those
4 lines become active. So this section will end up looking like

'======For DAO uncomment next 4 lines========
'====== comment out ADO below ========
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines========
'====== comment out DAO above ========
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

AND at the end of the function there will be another line to make active

'====== comment out next line for ADO ===========
Set db = Nothing

Then select Debug: Compile... from the menu. If the code compiles
successfully you are ready to go. If you get an error in this code, you will
probably need to add a library reference

-- SELECT Tools: References from the menu
-- Look to see if you have Microsoft DAO 3.6 Object Library checked
-- If not, then you will have to scroll down the list until you find it (the
3.6 may be a different number depending on which version you of Access you are
using.)
-- Check this reference and click on the ok button

Try to compile again.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
thanks agian. I didn't realize I had to download the module. I downloaded and
imported the module into my database. I am now getting the error compile
error user-defined type not defined. here is what is highlighted when I get
this message

rs As New ADODB.Recordset

This is my first experience with modules so bear with me please.



John said:
Did you go to the site and download the database and then extract the function
and paste it into a module in your database. If so, did you save the module
with a name OTHER than Concatenate. A module should never have the same name
as a function.

Here is the code for the function. Copy it and paste it into a vba module.

'================= Begin Function ======================
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
' Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines========
'====== comment out ADO below ========
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Set db = CurrentDb
Set db = DbEngine(0)(0) '<<<< Faster to create >>>>
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines========
'====== comment out DAO above ========
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing

'====== comment out next line for ADO ===========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

'================= END Function ======================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.
Thanks John-
Click to expand...
[quoted text clipped - 37 lines]
2 1 PAGE 1
2 2 PAGE 2
Click to expand...
Click to expand...
Click to expand...
 
A

AlanO

the module is running but it is only selecting the records with a CSITE_SEQ =
1. It doesn't seem to be appending the other records. here is the query as it
is curently written

SELECT test.SITE_ID, CONCATENATE("SELECT [book] FROM [test] WHERE SITE_ID ="
& SITE_ID & "
ORDER
BY CSITE_SEQ",Chr(13) & Chr(10)) AS Expr1
FROM test
WHERE (((test.CSITE_SEQ)=1));

I took off the where clause and it selected all of the records but it didn't
append the memo fields together.



John said:
Probably you will need to comment out that line and the next line
Just type an apostrophe at the beginning of the lines

On four lines above that you will need to remove the apostrophe so that those
4 lines become active. So this section will end up looking like

'======For DAO uncomment next 4 lines========
'====== comment out ADO below ========
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines========
'====== comment out DAO above ========
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

AND at the end of the function there will be another line to make active

'====== comment out next line for ADO ===========
Set db = Nothing

Then select Debug: Compile... from the menu. If the code compiles
successfully you are ready to go. If you get an error in this code, you will
probably need to add a library reference

-- SELECT Tools: References from the menu
-- Look to see if you have Microsoft DAO 3.6 Object Library checked
-- If not, then you will have to scroll down the list until you find it (the
3.6 may be a different number depending on which version you of Access you are
using.)
-- Check this reference and click on the ok button

Try to compile again.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
thanks agian. I didn't realize I had to download the module. I downloaded and
imported the module into my database. I am now getting the error compile
Click to expand...
[quoted text clipped - 73 lines]
Click to expand...
 
J

John Spencer

SELECT test.SITE_ID
, CONCATENATE("SELECT [book] FROM [test] WHERE SITE_ID ="
& SITE_ID & " ORDER BY CSITE_SEQ",Chr(13) & Chr(10)) AS Expr1
FROM test
WHERE (((test.CSITE_SEQ)=1));

I assume that every Site_ID has one record with a CSite_Seq equal to 1.
Based on that I assumed that you didn't want to repeat the concatenation for
EVERY record for a site, but only one.

Are you sure that it is not concatenating all the Book fields together? Did
you put the cursor in the result and then down arrow to see if the rest of the
data was there?

It worked for me when I tested it with one of my databases. I don't see
anything wrong with your query.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
the module is running but it is only selecting the records with a CSITE_SEQ =
1. It doesn't seem to be appending the other records. here is the query as it
is curently written

SELECT test.SITE_ID, CONCATENATE("SELECT [book] FROM [test] WHERE SITE_ID ="
& SITE_ID & "
ORDER
BY CSITE_SEQ",Chr(13) & Chr(10)) AS Expr1
FROM test
WHERE (((test.CSITE_SEQ)=1));

I took off the where clause and it selected all of the records but it didn't
append the memo fields together.



John said:
Probably you will need to comment out that line and the next line
Just type an apostrophe at the beginning of the lines

On four lines above that you will need to remove the apostrophe so that those
4 lines become active. So this section will end up looking like

'======For DAO uncomment next 4 lines========
'====== comment out ADO below ========
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines========
'====== comment out DAO above ========
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

AND at the end of the function there will be another line to make active

'====== comment out next line for ADO ===========
Set db = Nothing

Then select Debug: Compile... from the menu. If the code compiles
successfully you are ready to go. If you get an error in this code, you will
probably need to add a library reference

-- SELECT Tools: References from the menu
-- Look to see if you have Microsoft DAO 3.6 Object Library checked
-- If not, then you will have to scroll down the list until you find it (the
3.6 may be a different number depending on which version you of Access you are
using.)
-- Check this reference and click on the ok button

Try to compile again.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
thanks agian. I didn't realize I had to download the module. I downloaded and
imported the module into my database. I am now getting the error compile
Click to expand...
[quoted text clipped - 73 lines]
2 1 PAGE 1
2 2 PAGE 2
Click to expand...
Click to expand...
Click to expand...
 
A

AlanO via AccessMonster.com

John, thanks for all your help. I was able to get it to work. I replaced the
WHERE (((test.CSITE_SEQ)=1)) with GROUP BY SITE_ID. This seems to have gotten
me what I need.

Thanks again.


John said:
SELECT test.SITE_ID
, CONCATENATE("SELECT [book] FROM [test] WHERE SITE_ID ="
& SITE_ID & " ORDER BY CSITE_SEQ",Chr(13) & Chr(10)) AS Expr1
FROM test
WHERE (((test.CSITE_SEQ)=1));

I assume that every Site_ID has one record with a CSite_Seq equal to 1.
Based on that I assumed that you didn't want to repeat the concatenation for
EVERY record for a site, but only one.

Are you sure that it is not concatenating all the Book fields together? Did
you put the cursor in the result and then down arrow to see if the rest of the
data was there?

It worked for me when I tested it with one of my databases. I don't see
anything wrong with your query.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
the module is running but it is only selecting the records with a CSITE_SEQ =
1. It doesn't seem to be appending the other records. here is the query as it
Click to expand...
[quoted text clipped - 56 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Page numbering with Merged Document and IF statements 1
Need some PWA help 0
Creating Different Headers in Word 0
Finding the proper style field and the numbered paragraph value within the style 0
Memo fields in a query 10
Finding the right style field in VBA 0
Select Noted Memo Field 4
Automatically sending multiple parts of text from one word doc to another - is possible. 0

Top