Urgent for Duane Hookom re Concatenate

Noel · Oct 21, 2003

Hi. Can Duane or anyone else help here please? I have used
Duanes Concatenate program to allow me to group together
multiple child records in a Mail Merge. Everything works
well but now I have found that, with more than a certain
number of child records, the merge just stops mid record.
It seems to be related somehow to the number of characters
in the block of child records (around 219, ignoring Tabs)
but as these dont come from an actual field, I cant see
why it has a limit. For info, I have two queries. The
first builds a Student record grouping and spaces them
with Tabs as follows

Student: Chr$(9) & [Students Master].[FirstName] & " " &
[Surname] & Chr(9) & [Students Master].[Subject] & Chr(9)
& [Placements].[YearGroup]

Then in a second, the Concatenate program is called up to
allow the child records to be grouped and presented in the
Mail Merge document as field name Student Details as
follows

Student Details: Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr(13) &
Chr(10))

As I say, Student and Student Details arent actual fields
in a table, so dont have a set size. The queries show all
the child records in full so why do they get chopped off
when grouped in the MMerge. I cant see any sizing to be
done in the MMerge doc and it doesnt help if I provide
lots of space around the Student Details field. Any ideas?
Thanks, Noel

Michel Walsh · Oct 22, 2003

Hi,

That is probably 255, since a varchar field, or a Text field as Jet natively
refer to them, is limited to a maximum of 255 character ( or less, if you so
specify it so). The whole record is also limited to 4000 octets ( 2000 for
Jet 3.51, but that makes 2000 characters in both cases ). You count the
used space, not the reserved space, against that limit, and you exclude Memo
and OLE data fields.

=================Help File, under Access Specifications, Table
=================
Number of characters in a table or field description 255
Number of characters in a record (excluding Memo and OLE Object
fields) 2,000

=======================================================================

Hoping it may help,
Vanderghast, Access MVP

Noel · Oct 22, 2003

Thanks Michel, This clarifies it for me but are you saying
that I cant increase this limit? If not my users are going
to think Im crazy. My concatenate produces a set of
letters and in the body text a variable set of names and
other details (the child stuff). How can I tell them its
hit and miss as to whether all of their records appear?
Please tell me I can increase this limit. Cheers, Noel

-----Original Message-----
Hi,

That is probably 255, since a varchar field, or a Text field as Jet natively
refer to them, is limited to a maximum of 255 character ( or less, if you so
specify it so). The whole record is also limited to 4000 octets ( 2000 for
Jet 3.51, but that makes 2000 characters in both cases ). You count the
used space, not the reserved space, against that limit, and you exclude Memo
and OLE data fields.

=================Help File, under Access Specifications, Table
=================
Number of characters in a table or field description 255
Number of characters in a record (excluding Memo and OLE Object
fields) 2,000

========================================================== =============

Hoping it may help,
Vanderghast, Access MVP

Hi. Can Duane or anyone else help here please? I have used
Duanes Concatenate program to allow me to group together
multiple child records in a Mail Merge. Everything works
well but now I have found that, with more than a certain
number of child records, the merge just stops mid record.
It seems to be related somehow to the number of characters
in the block of child records (around 219, ignoring Tabs)
but as these dont come from an actual field, I cant see
why it has a limit. For info, I have two queries. The
first builds a Student record grouping and spaces them
with Tabs as follows

Student: Chr$(9) & [Students Master].[FirstName] & " " &
[Surname] & Chr(9) & [Students Master].[Subject] & Chr (9)
& [Placements].[YearGroup]

Then in a second, the Concatenate program is called up to
allow the child records to be grouped and presented in the
Mail Merge document as field name Student Details as
follows

Student Details: Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr(13) &
Chr(10))

As I say, Student and Student Details arent actual fields
in a table, so dont have a set size. The queries show all
the child records in full so why do they get chopped off
when grouped in the MMerge. I cant see any sizing to be
done in the MMerge doc and it doesnt help if I provide
lots of space around the Student Details field. Any ideas?
Thanks, Noel

Click to expand...

.

Michel Walsh · Oct 22, 2003

Hi,

You can't increase those limits, they are by design. Changing for a MEMO
would allow more than 255 characters, but you have to check if the code work
well with memo.

Hoping it may help,
Vanderghast, Access MVP

Noel said:
Thanks Michel, This clarifies it for me but are you saying
that I cant increase this limit? If not my users are going
to think Im crazy. My concatenate produces a set of
letters and in the body text a variable set of names and
other details (the child stuff). How can I tell them its
hit and miss as to whether all of their records appear?
Please tell me I can increase this limit. Cheers, Noel

-----Original Message-----
Hi,

That is probably 255, since a varchar field, or a Text field as Jet natively
refer to them, is limited to a maximum of 255 character ( or less, if you so
specify it so). The whole record is also limited to 4000 octets ( 2000 for
Jet 3.51, but that makes 2000 characters in both cases ). You count the
used space, not the reserved space, against that limit, and you exclude Memo
and OLE data fields.

=================Help File, under Access Specifications, Table
=================
Number of characters in a table or field description 255
Number of characters in a record (excluding Memo and OLE Object
fields) 2,000

========================================================== =============

Hoping it may help,
Vanderghast, Access MVP

Hi. Can Duane or anyone else help here please? I have used
Duanes Concatenate program to allow me to group together
multiple child records in a Mail Merge. Everything works
well but now I have found that, with more than a certain
number of child records, the merge just stops mid record.
It seems to be related somehow to the number of characters
in the block of child records (around 219, ignoring Tabs)
but as these dont come from an actual field, I cant see
why it has a limit. For info, I have two queries. The
first builds a Student record grouping and spaces them
with Tabs as follows

Student: Chr$(9) & [Students Master].[FirstName] & " " &
[Surname] & Chr(9) & [Students Master].[Subject] & Chr (9)
& [Placements].[YearGroup]

Then in a second, the Concatenate program is called up to
allow the child records to be grouped and presented in the
Mail Merge document as field name Student Details as
follows

Student Details: Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr(13) &
Chr(10))

As I say, Student and Student Details arent actual fields
in a table, so dont have a set size. The queries show all
the child records in full so why do they get chopped off
when grouped in the MMerge. I cant see any sizing to be
done in the MMerge doc and it doesnt help if I provide
lots of space around the Student Details field. Any ideas?
Thanks, Noel

Click to expand...

.

Click to expand...

Noel · Oct 22, 2003

Thanks Michel. Im not sure what i could change for memo.
The queries I am using build names like Student and
Student Details to group the contents of various text
fields but Students and Student Details arent actual
fields themselves, so presumably I cant make them memos
(or can I?). Do you mean I should change all the various
text fields, that are grouped together by the queries and
the Concatenate program, to memo? Will this effect the
data already in these fields? Thanks again, Noel

-----Original Message-----
Hi,

You can't increase those limits, they are by design. Changing for a MEMO
would allow more than 255 characters, but you have to check if the code work
well with memo.

Hoping it may help,
Vanderghast, Access MVP

Thanks Michel, This clarifies it for me but are you saying
that I cant increase this limit? If not my users are going
to think Im crazy. My concatenate produces a set of
letters and in the body text a variable set of names and
other details (the child stuff). How can I tell them its
hit and miss as to whether all of their records appear?
Please tell me I can increase this limit. Cheers, Noel

-----Original Message-----
Hi,

That is probably 255, since a varchar field, or a Text field as Jet natively
refer to them, is limited to a maximum of 255

Click to expand...

character (
or less, if you so

specify it so). The whole record is also limited to

Click to expand...

4000
octets ( 2000 for

Jet 3.51, but that makes 2000 characters in both cases ). You count the
used space, not the reserved space, against that limit, and you exclude Memo
and OLE data fields.

=================Help File, under Access

Click to expand...

Specifications,
Table

=================
Number of characters in a table or field description 255
Number of characters in a record (excluding Memo and OLE Object
fields) 2,000

Click to expand...

==========================================================

=============

Hoping it may help,
Vanderghast, Access MVP

Hi. Can Duane or anyone else help here please? I have used
Duanes Concatenate program to allow me to group together
multiple child records in a Mail Merge. Everything works
well but now I have found that, with more than a certain
number of child records, the merge just stops mid record.
It seems to be related somehow to the number of characters
in the block of child records (around 219, ignoring Tabs)
but as these dont come from an actual field, I cant see
why it has a limit. For info, I have two queries. The
first builds a Student record grouping and spaces them
with Tabs as follows

Student: Chr$(9) & [Students Master].[FirstName] & " " &
[Surname] & Chr(9) & [Students Master].[Subject] &

Click to expand...

Chr
(9)

& [Placements].[YearGroup]

Then in a second, the Concatenate program is called

Click to expand...

up
to

allow the child records to be grouped and presented

Click to expand...

in
the

Mail Merge document as field name Student Details as
follows

Student Details: Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr

Click to expand...

(13)
&

Chr(10))

As I say, Student and Student Details arent actual fields
in a table, so dont have a set size. The queries show all
the child records in full so why do they get chopped off
when grouped in the MMerge. I cant see any sizing to be
done in the MMerge doc and it doesnt help if I provide
lots of space around the Student Details field. Any ideas?
Thanks, Noel

.

Click to expand...

Click to expand...

.

Michel Walsh · Oct 22, 2003

Hi,

Only the field you try to get the big concatenation should be changed
into a Memo. I doubt a all-query solution would work, but if you use a
recordset based method, it may, and I think Duane's solution is based on a
recordset, if I remember.

Hoping it may help,
Vanderghast, Access MVP

Noel said:
Thanks Michel. Im not sure what i could change for memo.
The queries I am using build names like Student and
Student Details to group the contents of various text
fields but Students and Student Details arent actual
fields themselves, so presumably I cant make them memos
(or can I?). Do you mean I should change all the various
text fields, that are grouped together by the queries and
the Concatenate program, to memo? Will this effect the
data already in these fields? Thanks again, Noel

-----Original Message-----
Hi,

You can't increase those limits, they are by design. Changing for a MEMO
would allow more than 255 characters, but you have to check if the code work
well with memo.

Hoping it may help,
Vanderghast, Access MVP

Thanks Michel, This clarifies it for me but are you saying
that I cant increase this limit? If not my users are going
to think Im crazy. My concatenate produces a set of
letters and in the body text a variable set of names and
other details (the child stuff). How can I tell them its
hit and miss as to whether all of their records appear?
Please tell me I can increase this limit. Cheers, Noel
-----Original Message-----
Hi,

That is probably 255, since a varchar field, or a Text
field as Jet natively
refer to them, is limited to a maximum of 255 character (
or less, if you so
specify it so). The whole record is also limited to 4000
octets ( 2000 for
Jet 3.51, but that makes 2000 characters in both
cases ). You count the
used space, not the reserved space, against that limit,
and you exclude Memo
and OLE data fields.

=================Help File, under Access Specifications,
Table
=================
Number of characters in a table or field
description 255
Number of characters in a record (excluding Memo
and OLE Object
fields) 2,000

==========================================================
=============

Hoping it may help,
Vanderghast, Access MVP

message
Hi. Can Duane or anyone else help here please? I have
used
Duanes Concatenate program to allow me to group together
multiple child records in a Mail Merge. Everything works
well but now I have found that, with more than a certain
number of child records, the merge just stops mid
record.
It seems to be related somehow to the number of
characters
in the block of child records (around 219, ignoring
Tabs)
but as these dont come from an actual field, I cant see
why it has a limit. For info, I have two queries. The
first builds a Student record grouping and spaces them
with Tabs as follows

Student: Chr$(9) & [Students Master].[FirstName] & " " &
[Surname] & Chr(9) & [Students Master].[Subject] & Chr
(9)
& [Placements].[YearGroup]

Then in a second, the Concatenate program is called up
to
allow the child records to be grouped and presented in
the
Mail Merge document as field name Student Details as
follows

Student Details: Concatenate("SELECT Student FROM
[Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr (13)
&
Chr(10))

As I say, Student and Student Details arent actual
fields
in a table, so dont have a set size. The queries show
all
the child records in full so why do they get chopped off
when grouped in the MMerge. I cant see any sizing to be
done in the MMerge doc and it doesnt help if I provide
lots of space around the Student Details field. Any
ideas?
Thanks, Noel

.

Click to expand...

.

Click to expand...

Duane Hookom · Oct 23, 2003

Your query that contains the SchoolID should have only distinct records for
each school. Please post the full SQL of your query that uses the
Concatenate() function.

Noel · Oct 23, 2003

Thanks Michel. Sorry but Im not clever enough to
understand you. The concat program results in a field
called Student Details but this is not a real field in a
table. How can I change something that doesnt exist as a
field in a tabe to Memo. After all I didnt tell access to
make it a text field in the first place. Am I missing
something here (apart from a few brain cells that is).
Duane has replied now, so I have two experts advising me
which I appreciate. Thanks again. Cheers, Noel

-----Original Message-----
Hi,

Only the field you try to get the big concatenation should be changed
into a Memo. I doubt a all-query solution would work, but if you use a
recordset based method, it may, and I think Duane's solution is based on a
recordset, if I remember.

Hoping it may help,
Vanderghast, Access MVP

Thanks Michel. Im not sure what i could change for memo.
The queries I am using build names like Student and
Student Details to group the contents of various text
fields but Students and Student Details arent actual
fields themselves, so presumably I cant make them memos
(or can I?). Do you mean I should change all the various
text fields, that are grouped together by the queries and
the Concatenate program, to memo? Will this effect the
data already in these fields? Thanks again, Noel

-----Original Message-----
Hi,

You can't increase those limits, they are by

Click to expand...

design.
Changing for a MEMO

would allow more than 255 characters, but you have to check if the code work
well with memo.

Hoping it may help,
Vanderghast, Access MVP

Thanks Michel, This clarifies it for me but are you saying
that I cant increase this limit? If not my users are going
to think Im crazy. My concatenate produces a set of
letters and in the body text a variable set of names and
other details (the child stuff). How can I tell them its
hit and miss as to whether all of their records appear?
Please tell me I can increase this limit. Cheers, Noel
-----Original Message-----
Hi,

That is probably 255, since a varchar field, or a Text
field as Jet natively
refer to them, is limited to a maximum of 255 character (
or less, if you so
specify it so). The whole record is also limited to 4000
octets ( 2000 for
Jet 3.51, but that makes 2000 characters in both
cases ). You count the
used space, not the reserved space, against that limit,
and you exclude Memo
and OLE data fields.

=================Help File, under Access Specifications,
Table
=================
Number of characters in a table or field
description 255
Number of characters in a record (excluding Memo
and OLE Object
fields) 2,000

Click to expand...

==========================================================

=============

Hoping it may help,
Vanderghast, Access MVP

message
Hi. Can Duane or anyone else help here please? I have
used
Duanes Concatenate program to allow me to group together
multiple child records in a Mail Merge. Everything works
well but now I have found that, with more than a certain
number of child records, the merge just stops mid
record.
It seems to be related somehow to the number of
characters
in the block of child records (around 219, ignoring
Tabs)
but as these dont come from an actual field, I

Click to expand...

cant
see

why it has a limit. For info, I have two queries. The
first builds a Student record grouping and spaces them
with Tabs as follows

Student: Chr$(9) & [Students Master].[FirstName] & " " &
[Surname] & Chr(9) & [Students Master].[Subject] & Chr
(9)
& [Placements].[YearGroup]

Then in a second, the Concatenate program is

Click to expand...

called
up

to
allow the child records to be grouped and

Click to expand...

presented
in

the
Mail Merge document as field name Student Details as
follows

Student Details: Concatenate("SELECT Student FROM
[Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].

Click to expand...

[SchoolID],Chr
(13)

&
Chr(10))

As I say, Student and Student Details arent actual
fields
in a table, so dont have a set size. The queries show
all
the child records in full so why do they get

Click to expand...

chopped
off

when grouped in the MMerge. I cant see any sizing

Click to expand...

to
be

done in the MMerge doc and it doesnt help if I provide
lots of space around the Student Details field. Any
ideas?
Thanks, Noel

.

.

Click to expand...

Click to expand...

.

Duane Hookom · Oct 24, 2003

You can't ever get more than 255 characters in a field/column/expression
that you are using with Group By. Either change the Group By to First or
redo you query so that you don't group by.

--
Duane Hookom
MS Access MVP

Noel said:
Thanks for replying. Ive now proved that Im hitting some
sort of limit, presumably the 255 chars that Michel
mentions.If I have up to say 8 or 9 child records its OK
but over that its not safe to assume all record details
will appear. Otherwise the Concat program works fine.
Anyway, Ive decided to post you two SQL listings. The
first one is from the first query, the one that bunches
the student name, subject and yeargroup together with tab
spacing and names this Student. The second is from the
query that runs the Concatenate program on this Student
grouping and calls the result Student Details. In his
post, Michel seems to be suggesting that I change Student
Details to a Memo but its not a real field in a table so
Im confused. Ill ask him to clarify. Thanks again for your
help.

First SQL:

SELECT [Schools and Offers].*, Placements.*, [Students
Master].*, Placements.PlacementStage, Chr$(9) & [Students
Master].[FirstName] & " " & [Surname] & Chr(9) & [Students
Master].[Subject] & Chr(9) & [Placements].[YearGroup] AS
Student, [Schools and Offers].FirstName, [Students
Master].Course, [Students Master].CourseYear, [Students
Master].Archive, [Students Master].Status,
Placements.Result, [Students Master].Subject, [Schools and
Offers].SchoolName, Placements.[Notes-Trans&Accom], [Rolle
Tutors].RolleTutorFirstName, [Rolle
Tutors].RolleTutorSecondName, Mentors.MentorFirstName,
Mentors.MentorSecondName, Placements.Status, [Rolle
Tutors].RolleTutorID, Mentors.MentorID,
Placements.PairedTutor
FROM [Students Master] INNER JOIN ([Schools and Offers]
INNER JOIN ([Rolle Tutors] RIGHT JOIN (Mentors RIGHT JOIN
Placements ON Mentors.MentorID = Placements.MentorID) ON
[Rolle Tutors].RolleTutorID = Placements.RolleTutorID) ON
[Schools and Offers].SchoolID = Placements.SchoolID) ON
[Students Master].StudentID = Placements.StudentID
WHERE (((Placements.PlacementStage)="SBW") AND (([Students
Master].Course)="BEd") AND (([Students Master].CourseYear)
="Year 1") AND (([Students Master].Archive)=No) AND
(([Students Master].Status)="Live") AND
((Placements.Result) Is Null))
ORDER BY [Schools and Offers].SchoolName;

Second SQL:

SELECT [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select Placements BEd Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW].[Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1 SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements BEd Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr(13) &
Chr(10)) AS [Student Details], Format(Date(),"dd mmmm
yyyy") AS [Date]
FROM [Select Placements BEd Yr1 SBW]
GROUP BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select Placements BEd Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW].[Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1 SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements BEd Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr(13) &
Chr(10)), Format(Date(),"dd mmmm yyyy")
ORDER BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolName;

-----Original Message-----
Your query that contains the SchoolID should have only distinct records for
each school. Please post the full SQL of your query that uses the
Concatenate() function.

--
Duane Hookom
MS Access MVP

Hi. Can Duane or anyone else help here please? I have used
Duanes Concatenate program to allow me to group together
multiple child records in a Mail Merge. Everything works
well but now I have found that, with more than a certain
number of child records, the merge just stops mid record.
It seems to be related somehow to the number of characters
in the block of child records (around 219, ignoring Tabs)
but as these dont come from an actual field, I cant see
why it has a limit. For info, I have two queries. The
first builds a Student record grouping and spaces them
with Tabs as follows

Student: Chr$(9) & [Students Master].[FirstName] & " " &
[Surname] & Chr(9) & [Students Master].[Subject] & Chr (9)
& [Placements].[YearGroup]

Then in a second, the Concatenate program is called up to
allow the child records to be grouped and presented in the
Mail Merge document as field name Student Details as
follows

Student Details: Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr(13) &
Chr(10))

As I say, Student and Student Details arent actual fields
in a table, so dont have a set size. The queries show all
the child records in full so why do they get chopped off
when grouped in the MMerge. I cant see any sizing to be
done in the MMerge doc and it doesnt help if I provide
lots of space around the Student Details field. Any ideas?
Thanks, Noel

Click to expand...

.

Click to expand...

Noel · Oct 24, 2003

Thanks Duane. Hmmmmm thats given me some hope at least.
Ill have a play around with that info and will get back to
you - might be Tuesday of next week though. Cheers, Noel

-----Original Message-----
You can't ever get more than 255 characters in a field/column/expression
that you are using with Group By. Either change the Group By to First or
redo you query so that you don't group by.

--
Duane Hookom
MS Access MVP

Thanks for replying. Ive now proved that Im hitting some
sort of limit, presumably the 255 chars that Michel
mentions.If I have up to say 8 or 9 child records its OK
but over that its not safe to assume all record details
will appear. Otherwise the Concat program works fine.
Anyway, Ive decided to post you two SQL listings. The
first one is from the first query, the one that bunches
the student name, subject and yeargroup together with tab
spacing and names this Student. The second is from the
query that runs the Concatenate program on this Student
grouping and calls the result Student Details. In his
post, Michel seems to be suggesting that I change Student
Details to a Memo but its not a real field in a table so
Im confused. Ill ask him to clarify. Thanks again for your
help.

First SQL:

SELECT [Schools and Offers].*, Placements.*, [Students
Master].*, Placements.PlacementStage, Chr$(9) & [Students
Master].[FirstName] & " " & [Surname] & Chr(9) & [Students
Master].[Subject] & Chr(9) & [Placements].[YearGroup] AS
Student, [Schools and Offers].FirstName, [Students
Master].Course, [Students Master].CourseYear, [Students
Master].Archive, [Students Master].Status,
Placements.Result, [Students Master].Subject, [Schools and
Offers].SchoolName, Placements.[Notes-Trans&Accom], [Rolle
Tutors].RolleTutorFirstName, [Rolle
Tutors].RolleTutorSecondName, Mentors.MentorFirstName,
Mentors.MentorSecondName, Placements.Status, [Rolle
Tutors].RolleTutorID, Mentors.MentorID,
Placements.PairedTutor
FROM [Students Master] INNER JOIN ([Schools and Offers]
INNER JOIN ([Rolle Tutors] RIGHT JOIN (Mentors RIGHT JOIN
Placements ON Mentors.MentorID = Placements.MentorID) ON
[Rolle Tutors].RolleTutorID = Placements.RolleTutorID) ON
[Schools and Offers].SchoolID = Placements.SchoolID) ON
[Students Master].StudentID = Placements.StudentID
WHERE (((Placements.PlacementStage)="SBW") AND (([Students
Master].Course)="BEd") AND (([Students Master].CourseYear)
="Year 1") AND (([Students Master].Archive)=No) AND
(([Students Master].Status)="Live") AND
((Placements.Result) Is Null))
ORDER BY [Schools and Offers].SchoolName;

Second SQL:

SELECT [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select Placements BEd Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW]. [Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1 SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements BEd Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr(13) &
Chr(10)) AS [Student Details], Format(Date(),"dd mmmm
yyyy") AS [Date]
FROM [Select Placements BEd Yr1 SBW]
GROUP BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select Placements BEd Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW]. [Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1 SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements BEd Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr(13) &
Chr(10)), Format(Date(),"dd mmmm yyyy")
ORDER BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolName;

-----Original Message-----
Your query that contains the SchoolID should have only distinct records for
each school. Please post the full SQL of your query

Click to expand...

that
uses the

Concatenate() function.

--
Duane Hookom
MS Access MVP

Hi. Can Duane or anyone else help here please? I have used
Duanes Concatenate program to allow me to group together
multiple child records in a Mail Merge. Everything works
well but now I have found that, with more than a certain
number of child records, the merge just stops mid record.
It seems to be related somehow to the number of characters
in the block of child records (around 219, ignoring Tabs)
but as these dont come from an actual field, I cant see
why it has a limit. For info, I have two queries. The
first builds a Student record grouping and spaces them
with Tabs as follows

Student: Chr$(9) & [Students Master].[FirstName] & " " &
[Surname] & Chr(9) & [Students Master].[Subject] &

Click to expand...

Chr
(9)

& [Placements].[YearGroup]

Then in a second, the Concatenate program is called

Click to expand...

up
to

allow the child records to be grouped and presented

Click to expand...

in
the

Mail Merge document as field name Student Details as
follows

Student Details: Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr

Click to expand...

(13)
&

Chr(10))

As I say, Student and Student Details arent actual fields
in a table, so dont have a set size. The queries show all
the child records in full so why do they get chopped off
when grouped in the MMerge. I cant see any sizing to be
done in the MMerge doc and it doesnt help if I provide
lots of space around the Student Details field. Any ideas?
Thanks, Noel

.

Click to expand...

Click to expand...

.

Noel · Oct 25, 2003

Hi again Duane. Ive tried what you advised and it still
doesn’t work. I took out Group By in my final query (the
other one doesn’t use Group By), then tried using First
instead. The result in both cases was the same, my Child
data is cut off exactly as before. One odd thing I
noticed, when I look at the results of the query in
datasheet view (whether or not I use Group By or First or
neither), I can step through the concatenated child
records in the Student Details field and all the data is
there. Does this not suggest that the query and Concat
program is working OK and that somehow the problem is
something to do with Access not being able to pass all
this info to the MM doc? Or does the datasheet view not
show the actual results of the query running? Any further
help would be appreciated. I now have a small sample
database I build here at home which illustrates the
problem. Would it help if you have a look at it? Thanks
again, Noel

-----Original Message-----
Thanks Duane. Hmmmmm thats given me some hope at least.
Ill have a play around with that info and will get back to
you - might be Tuesday of next week though. Cheers, Noel

-----Original Message-----
You can't ever get more than 255 characters in a field/column/expression
that you are using with Group By. Either change the

Click to expand...

Group
By to First or

redo you query so that you don't group by.

--
Duane Hookom
MS Access MVP

Thanks for replying. Ive now proved that Im hitting some
sort of limit, presumably the 255 chars that Michel
mentions.If I have up to say 8 or 9 child records its OK
but over that its not safe to assume all record details
will appear. Otherwise the Concat program works fine.
Anyway, Ive decided to post you two SQL listings. The
first one is from the first query, the one that bunches
the student name, subject and yeargroup together with tab
spacing and names this Student. The second is from the
query that runs the Concatenate program on this Student
grouping and calls the result Student Details. In his
post, Michel seems to be suggesting that I change Student
Details to a Memo but its not a real field in a table so
Im confused. Ill ask him to clarify. Thanks again for your
help.

First SQL:

SELECT [Schools and Offers].*, Placements.*, [Students
Master].*, Placements.PlacementStage, Chr$(9) & [Students
Master].[FirstName] & " " & [Surname] & Chr(9) & [Students
Master].[Subject] & Chr(9) & [Placements].[YearGroup] AS
Student, [Schools and Offers].FirstName, [Students
Master].Course, [Students Master].CourseYear, [Students
Master].Archive, [Students Master].Status,
Placements.Result, [Students Master].Subject, [Schools and
Offers].SchoolName, Placements.[Notes-Trans&Accom], [Rolle
Tutors].RolleTutorFirstName, [Rolle
Tutors].RolleTutorSecondName, Mentors.MentorFirstName,
Mentors.MentorSecondName, Placements.Status, [Rolle
Tutors].RolleTutorID, Mentors.MentorID,
Placements.PairedTutor
FROM [Students Master] INNER JOIN ([Schools and Offers]
INNER JOIN ([Rolle Tutors] RIGHT JOIN (Mentors RIGHT JOIN
Placements ON Mentors.MentorID = Placements.MentorID) ON
[Rolle Tutors].RolleTutorID = Placements.RolleTutorID) ON
[Schools and Offers].SchoolID = Placements.SchoolID) ON
[Students Master].StudentID = Placements.StudentID
WHERE (((Placements.PlacementStage)="SBW") AND (([Students
Master].Course)="BEd") AND (([Students Master].CourseYear)
="Year 1") AND (([Students Master].Archive)=No) AND
(([Students Master].Status)="Live") AND
((Placements.Result) Is Null))
ORDER BY [Schools and Offers].SchoolName;

Second SQL:

SELECT [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select Placements BEd Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW]. [Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1 SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements BEd Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr

Click to expand...

Click to expand...

(13)

&

Chr(10)) AS [Student Details], Format(Date(),"dd mmmm
yyyy") AS [Date]
FROM [Select Placements BEd Yr1 SBW]
GROUP BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select Placements BEd Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW]. [Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1 SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements BEd Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr

Click to expand...

Click to expand...

(13)

&

Chr(10)), Format(Date(),"dd mmmm yyyy")
ORDER BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolName;

-----Original Message-----
Your query that contains the SchoolID should have only
distinct records for
each school. Please post the full SQL of your query that
uses the
Concatenate() function.

--
Duane Hookom
MS Access MVP

message
Hi. Can Duane or anyone else help here please? I have
used
Duanes Concatenate program to allow me to group together
multiple child records in a Mail Merge. Everything works
well but now I have found that, with more than a certain
number of child records, the merge just stops mid
record.
It seems to be related somehow to the number of
characters
in the block of child records (around 219, ignoring
Tabs)
but as these dont come from an actual field, I cant see
why it has a limit. For info, I have two queries. The
first builds a Student record grouping and spaces them
with Tabs as follows

Student: Chr$(9) & [Students Master].[FirstName] & " " &
[Surname] & Chr(9) & [Students Master].[Subject] & Chr
(9)
& [Placements].[YearGroup]

Then in a second, the Concatenate program is called up
to
allow the child records to be grouped and presented in
the
Mail Merge document as field name Student Details as
follows

Student Details: Concatenate("SELECT Student FROM
[Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr (13)
&
Chr(10))

As I say, Student and Student Details arent actual
fields
in a table, so dont have a set size. The queries show
all
the child records in full so why do they get

Click to expand...

Click to expand...

chopped

off

Click to expand...

to

be

.

Click to expand...

.

Duane Hookom · Oct 25, 2003

This sounds like a Access to Word issue rather than a problem with the
Concatenate function.
When I do mail merges, I always export the data to a delimited file. I then
set the data source of the merge within Word to the file.

--
Duane Hookom
Microsoft Access MVP

Noel said:
Hi again Duane. Ive tried what you advised and it still
doesn’t work. I took out Group By in my final query (the
other one doesn’t use Group By), then tried using First
instead. The result in both cases was the same, my Child
data is cut off exactly as before. One odd thing I
noticed, when I look at the results of the query in
datasheet view (whether or not I use Group By or First or
neither), I can step through the concatenated child
records in the Student Details field and all the data is
there. Does this not suggest that the query and Concat
program is working OK and that somehow the problem is
something to do with Access not being able to pass all
this info to the MM doc? Or does the datasheet view not
show the actual results of the query running? Any further
help would be appreciated. I now have a small sample
database I build here at home which illustrates the
problem. Would it help if you have a look at it? Thanks
again, Noel

-----Original Message-----
Thanks Duane. Hmmmmm thats given me some hope at least.
Ill have a play around with that info and will get back to
you - might be Tuesday of next week though. Cheers, Noel

-----Original Message-----
You can't ever get more than 255 characters in a field/column/expression
that you are using with Group By. Either change the

Click to expand...

Group
By to First or

redo you query so that you don't group by.

--
Duane Hookom
MS Access MVP

Thanks for replying. Ive now proved that Im hitting some
sort of limit, presumably the 255 chars that Michel
mentions.If I have up to say 8 or 9 child records its OK
but over that its not safe to assume all record details
will appear. Otherwise the Concat program works fine.
Anyway, Ive decided to post you two SQL listings. The
first one is from the first query, the one that bunches
the student name, subject and yeargroup together with tab
spacing and names this Student. The second is from the
query that runs the Concatenate program on this Student
grouping and calls the result Student Details. In his
post, Michel seems to be suggesting that I change Student
Details to a Memo but its not a real field in a table so
Im confused. Ill ask him to clarify. Thanks again for your
help.

First SQL:

SELECT [Schools and Offers].*, Placements.*, [Students
Master].*, Placements.PlacementStage, Chr$(9) & [Students
Master].[FirstName] & " " & [Surname] & Chr(9) & [Students
Master].[Subject] & Chr(9) & [Placements].[YearGroup] AS
Student, [Schools and Offers].FirstName, [Students
Master].Course, [Students Master].CourseYear, [Students
Master].Archive, [Students Master].Status,
Placements.Result, [Students Master].Subject, [Schools and
Offers].SchoolName, Placements.[Notes-Trans&Accom], [Rolle
Tutors].RolleTutorFirstName, [Rolle
Tutors].RolleTutorSecondName, Mentors.MentorFirstName,
Mentors.MentorSecondName, Placements.Status, [Rolle
Tutors].RolleTutorID, Mentors.MentorID,
Placements.PairedTutor
FROM [Students Master] INNER JOIN ([Schools and Offers]
INNER JOIN ([Rolle Tutors] RIGHT JOIN (Mentors RIGHT JOIN
Placements ON Mentors.MentorID = Placements.MentorID) ON
[Rolle Tutors].RolleTutorID = Placements.RolleTutorID) ON
[Schools and Offers].SchoolID = Placements.SchoolID) ON
[Students Master].StudentID = Placements.StudentID
WHERE (((Placements.PlacementStage)="SBW") AND (([Students
Master].Course)="BEd") AND (([Students Master].CourseYear)
="Year 1") AND (([Students Master].Archive)=No) AND
(([Students Master].Status)="Live") AND
((Placements.Result) Is Null))
ORDER BY [Schools and Offers].SchoolName;

Second SQL:

SELECT [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select Placements BEd Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW]. [Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1 SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements BEd Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr

Click to expand...

(13)

&
Chr(10)) AS [Student Details], Format(Date(),"dd mmmm
yyyy") AS [Date]
FROM [Select Placements BEd Yr1 SBW]
GROUP BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select Placements BEd Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW]. [Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1 SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements BEd Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr

Click to expand...

(13)

&
Chr(10)), Format(Date(),"dd mmmm yyyy")
ORDER BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolName;

-----Original Message-----
Your query that contains the SchoolID should have only
distinct records for
each school. Please post the full SQL of your query that
uses the
Concatenate() function.

--
Duane Hookom
MS Access MVP

message
Hi. Can Duane or anyone else help here please? I have
used
Duanes Concatenate program to allow me to group together
multiple child records in a Mail Merge. Everything works
well but now I have found that, with more than a certain
number of child records, the merge just stops mid
record.
It seems to be related somehow to the number of
characters
in the block of child records (around 219, ignoring
Tabs)
but as these dont come from an actual field, I cant see
why it has a limit. For info, I have two queries. The
first builds a Student record grouping and spaces them
with Tabs as follows

Student: Chr$(9) & [Students Master].[FirstName] & " " &
[Surname] & Chr(9) & [Students Master].[Subject] & Chr
(9)
& [Placements].[YearGroup]

Then in a second, the Concatenate program is called up
to
allow the child records to be grouped and presented in
the
Mail Merge document as field name Student Details as
follows

Student Details: Concatenate("SELECT Student FROM
[Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr (13)
&
Chr(10))

As I say, Student and Student Details arent actual
fields
in a table, so dont have a set size. The queries show
all
the child records in full so why do they get

Click to expand...

chopped

off
when grouped in the MMerge. I cant see any sizing

Click to expand...

to

be
done in the MMerge doc and it doesnt help if I provide
lots of space around the Student Details field. Any
ideas?
Thanks, Noel

.

.

Click to expand...

.

Click to expand...

Noel · Oct 25, 2003

Thanks Duane. Again something new to get my head around.
Ill try playing with that tomorrow. For now could you
expand a little please? In my case I have a command button
in my db which uses a hyperlink address to open the MM
document, which has been created and linked to the
concatenate query. If I get your suggestion to work for
me, can I still set up an automatic link to the MM doc and
if so how? (Im not sure how I could do this with the need
to use an intermediary delimited file). Many thanks, Noel

-----Original Message-----
This sounds like a Access to Word issue rather than a problem with the
Concatenate function.
When I do mail merges, I always export the data to a delimited file. I then
set the data source of the merge within Word to the file.

--
Duane Hookom
Microsoft Access MVP

Hi again Duane. Ive tried what you advised and it still
doesn’t work. I took out Group By in my final query (the
other one doesn’t use Group By), then tried using First
instead. The result in both cases was the same, my Child
data is cut off exactly as before. One odd thing I
noticed, when I look at the results of the query in
datasheet view (whether or not I use Group By or First or
neither), I can step through the concatenated child
records in the Student Details field and all the data is
there. Does this not suggest that the query and Concat
program is working OK and that somehow the problem is
something to do with Access not being able to pass all
this info to the MM doc? Or does the datasheet view not
show the actual results of the query running? Any further
help would be appreciated. I now have a small sample
database I build here at home which illustrates the
problem. Would it help if you have a look at it? Thanks
again, Noel

-----Original Message-----
Thanks Duane. Hmmmmm thats given me some hope at least.
Ill have a play around with that info and will get back to
you - might be Tuesday of next week though. Cheers, Noel
-----Original Message-----
You can't ever get more than 255 characters in a
field/column/expression
that you are using with Group By. Either change the Group
By to First or
redo you query so that you don't group by.

--
Duane Hookom
MS Access MVP

message
Thanks for replying. Ive now proved that Im hitting some
sort of limit, presumably the 255 chars that Michel
mentions.If I have up to say 8 or 9 child records

Click to expand...

its
OK

but over that its not safe to assume all record details
will appear. Otherwise the Concat program works fine.
Anyway, Ive decided to post you two SQL listings. The
first one is from the first query, the one that bunches
the student name, subject and yeargroup together with
tab
spacing and names this Student. The second is from the
query that runs the Concatenate program on this Student
grouping and calls the result Student Details. In his
post, Michel seems to be suggesting that I change
Student
Details to a Memo but its not a real field in a

Click to expand...

table
so

Im confused. Ill ask him to clarify. Thanks again for
your
help.

First SQL:

SELECT [Schools and Offers].*, Placements.*, [Students
Master].*, Placements.PlacementStage, Chr$(9) &
[Students
Master].[FirstName] & " " & [Surname] & Chr(9) &
[Students
Master].[Subject] & Chr(9) & [Placements].

Click to expand...

[YearGroup]
AS

Student, [Schools and Offers].FirstName, [Students
Master].Course, [Students Master].CourseYear, [Students
Master].Archive, [Students Master].Status,
Placements.Result, [Students Master].Subject, [Schools
and
Offers].SchoolName, Placements.[Notes-Trans&Accom],
[Rolle
Tutors].RolleTutorFirstName, [Rolle
Tutors].RolleTutorSecondName, Mentors.MentorFirstName,
Mentors.MentorSecondName, Placements.Status, [Rolle
Tutors].RolleTutorID, Mentors.MentorID,
Placements.PairedTutor
FROM [Students Master] INNER JOIN ([Schools and Offers]
INNER JOIN ([Rolle Tutors] RIGHT JOIN (Mentors RIGHT
JOIN
Placements ON Mentors.MentorID =

Click to expand...

Placements.MentorID)
ON

[Rolle Tutors].RolleTutorID = Placements.RolleTutorID)
ON
[Schools and Offers].SchoolID = Placements.SchoolID) ON
[Students Master].StudentID = Placements.StudentID
WHERE (((Placements.PlacementStage)="SBW") AND
(([Students
Master].Course)="BEd") AND (([Students
Master].CourseYear)
="Year 1") AND (([Students Master].Archive)=No) AND
(([Students Master].Status)="Live") AND
((Placements.Result) Is Null))
ORDER BY [Schools and Offers].SchoolName;

Second SQL:

SELECT [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select Placements BEd
Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW].
[Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1
SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements BEd
Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr (13)
&
Chr(10)) AS [Student Details], Format(Date(),"dd mmmm
yyyy") AS [Date]
FROM [Select Placements BEd Yr1 SBW]
GROUP BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select Placements BEd
Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW].
[Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1
SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements BEd
Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr (13)
&
Chr(10)), Format(Date(),"dd mmmm yyyy")
ORDER BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolName;

-----Original Message-----
Your query that contains the SchoolID should have only
distinct records for
each school. Please post the full SQL of your query
that
uses the
Concatenate() function.

--
Duane Hookom
MS Access MVP

message
Hi. Can Duane or anyone else help here please? I have
used
Duanes Concatenate program to allow me to group
together
multiple child records in a Mail Merge. Everything
works
well but now I have found that, with more than a
certain
number of child records, the merge just stops mid
record.
It seems to be related somehow to the number of
characters
in the block of child records (around 219, ignoring
Tabs)
but as these dont come from an actual field, I cant
see
why it has a limit. For info, I have two queries. The
first builds a Student record grouping and spaces
them
with Tabs as follows

Student: Chr$(9) & [Students Master].[FirstName]
& " " &
[Surname] & Chr(9) & [Students Master].[Subject] &
Chr
(9)
& [Placements].[YearGroup]

Then in a second, the Concatenate program is called
up
to
allow the child records to be grouped and presented
in
the
Mail Merge document as field name Student Details as
follows

Student Details: Concatenate("SELECT Student FROM
[Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers]. [SchoolID],Chr
(13)
&
Chr(10))

As I say, Student and Student Details arent actual
fields
in a table, so dont have a set size. The queries show
all
the child records in full so why do they get chopped
off
when grouped in the MMerge. I cant see any sizing to
be
done in the MMerge doc and it doesnt help if I
provide
lots of space around the Student Details field. Any
ideas?
Thanks, Noel

.

.

.

Click to expand...

Click to expand...

.

Duane Hookom · Oct 25, 2003

I just save/export the query to a text or Word Merge file. You would then
need to change the data source from Access to the file. If you save the Word
main document, the next time you open it, you will be connecting to the same
data source file.

--
Duane Hookom
MS Access MVP

Noel said:
Thanks Duane. Again something new to get my head around.
Ill try playing with that tomorrow. For now could you
expand a little please? In my case I have a command button
in my db which uses a hyperlink address to open the MM
document, which has been created and linked to the
concatenate query. If I get your suggestion to work for
me, can I still set up an automatic link to the MM doc and
if so how? (Im not sure how I could do this with the need
to use an intermediary delimited file). Many thanks, Noel

-----Original Message-----
This sounds like a Access to Word issue rather than a problem with the
Concatenate function.
When I do mail merges, I always export the data to a delimited file. I then
set the data source of the merge within Word to the file.

--
Duane Hookom
Microsoft Access MVP

Hi again Duane. Ive tried what you advised and it still
doesn’t work. I took out Group By in my final query (the
other one doesn’t use Group By), then tried using First
instead. The result in both cases was the same, my Child
data is cut off exactly as before. One odd thing I
noticed, when I look at the results of the query in
datasheet view (whether or not I use Group By or First or
neither), I can step through the concatenated child
records in the Student Details field and all the data is
there. Does this not suggest that the query and Concat
program is working OK and that somehow the problem is
something to do with Access not being able to pass all
this info to the MM doc? Or does the datasheet view not
show the actual results of the query running? Any further
help would be appreciated. I now have a small sample
database I build here at home which illustrates the
problem. Would it help if you have a look at it? Thanks
again, Noel

-----Original Message-----
Thanks Duane. Hmmmmm thats given me some hope at least.
Ill have a play around with that info and will get back
to
you - might be Tuesday of next week though. Cheers, Noel
-----Original Message-----
You can't ever get more than 255 characters in a
field/column/expression
that you are using with Group By. Either change the
Group
By to First or
redo you query so that you don't group by.

--
Duane Hookom
MS Access MVP

message
Thanks for replying. Ive now proved that Im hitting
some
sort of limit, presumably the 255 chars that Michel
mentions.If I have up to say 8 or 9 child records its
OK
but over that its not safe to assume all record details
will appear. Otherwise the Concat program works fine.
Anyway, Ive decided to post you two SQL listings. The
first one is from the first query, the one that bunches
the student name, subject and yeargroup together with
tab
spacing and names this Student. The second is from the
query that runs the Concatenate program on this Student
grouping and calls the result Student Details. In his
post, Michel seems to be suggesting that I change
Student
Details to a Memo but its not a real field in a table
so
Im confused. Ill ask him to clarify. Thanks again for
your
help.

First SQL:

SELECT [Schools and Offers].*, Placements.*, [Students
Master].*, Placements.PlacementStage, Chr$(9) &
[Students
Master].[FirstName] & " " & [Surname] & Chr(9) &
[Students
Master].[Subject] & Chr(9) & [Placements]. [YearGroup]
AS
Student, [Schools and Offers].FirstName, [Students
Master].Course, [Students Master].CourseYear, [Students
Master].Archive, [Students Master].Status,
Placements.Result, [Students Master].Subject, [Schools
and
Offers].SchoolName, Placements.[Notes-Trans&Accom],
[Rolle
Tutors].RolleTutorFirstName, [Rolle
Tutors].RolleTutorSecondName, Mentors.MentorFirstName,
Mentors.MentorSecondName, Placements.Status, [Rolle
Tutors].RolleTutorID, Mentors.MentorID,
Placements.PairedTutor
FROM [Students Master] INNER JOIN ([Schools and Offers]
INNER JOIN ([Rolle Tutors] RIGHT JOIN (Mentors RIGHT
JOIN
Placements ON Mentors.MentorID = Placements.MentorID)
ON
[Rolle Tutors].RolleTutorID = Placements.RolleTutorID)
ON
[Schools and Offers].SchoolID = Placements.SchoolID) ON
[Students Master].StudentID = Placements.StudentID
WHERE (((Placements.PlacementStage)="SBW") AND
(([Students
Master].Course)="BEd") AND (([Students
Master].CourseYear)
="Year 1") AND (([Students Master].Archive)=No) AND
(([Students Master].Status)="Live") AND
((Placements.Result) Is Null))
ORDER BY [Schools and Offers].SchoolName;

Second SQL:

SELECT [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select Placements BEd
Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW].
[Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1
SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements BEd
Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr
(13)
&
Chr(10)) AS [Student Details], Format(Date(),"dd mmmm
yyyy") AS [Date]
FROM [Select Placements BEd Yr1 SBW]
GROUP BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select Placements BEd
Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW].
[Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1
SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements BEd
Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers].[SchoolID],Chr
(13)
&
Chr(10)), Format(Date(),"dd mmmm yyyy")
ORDER BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolName;

-----Original Message-----
Your query that contains the SchoolID should have only
distinct records for
each school. Please post the full SQL of your query
that
uses the
Concatenate() function.

--
Duane Hookom
MS Access MVP

message
Hi. Can Duane or anyone else help here please? I
have
used
Duanes Concatenate program to allow me to group
together
multiple child records in a Mail Merge. Everything
works
well but now I have found that, with more than a
certain
number of child records, the merge just stops mid
record.
It seems to be related somehow to the number of
characters
in the block of child records (around 219, ignoring
Tabs)
but as these dont come from an actual field, I cant
see
why it has a limit. For info, I have two queries.
The
first builds a Student record grouping and spaces
them
with Tabs as follows

Student: Chr$(9) & [Students Master].[FirstName]
& " " &
[Surname] & Chr(9) & [Students Master].[Subject] &
Chr
(9)
& [Placements].[YearGroup]

Then in a second, the Concatenate program is called
up
to
allow the child records to be grouped and presented
in
the
Mail Merge document as field name Student Details as
follows

Student Details: Concatenate("SELECT Student FROM
[Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers]. [SchoolID],Chr
(13)
&
Chr(10))

As I say, Student and Student Details arent actual
fields
in a table, so dont have a set size. The queries
show
all
the child records in full so why do they get
chopped
off
when grouped in the MMerge. I cant see any sizing
to
be
done in the MMerge doc and it doesnt help if I
provide
lots of space around the Student Details field. Any
ideas?
Thanks, Noel

.

.

.

Click to expand...

.

Click to expand...

Noel · Oct 26, 2003

Thanks Duane. Ive just tried your last suggestion and Im
afraid it doesnt work. The data exported to the delimited
file is exactly the same as that shown on the MM docs - it
cuts off midway through one of the child records - it
looks like the datasheet view of the Concat query isnt
really showing the true results of the query running and
the problem (or limitation) is within the query. Can you
offer any further advice? Surely Im not the only person
who wants a significant amount of Child record data to
come out in a MM doc?

Perhaps a different approach would work. What do you think
of my way of preparing the Student Details by linking
Name, then Subject then YearGroup, separated by tabs and
ending with carriage return then passing this as one field
to your Concat program? This is the only way I can think
of getting the MM doc to show the relevant child record
fields one above another. Is there another way of doing
this that may avoid the problem Im getting, cos doing it
my way, the MM is having to deal with one big field. Is
there a way of getting the child record fields to line up
in columns if I were to offer separate fields for
StudentName, Subject etc to your Concat program? That way
each separate field would have its own 255 Chr limitation
which would not be a problem. Have we run dry on the Group
By/First theory? Thanks again for your time, Noel

-----Original Message-----
I just save/export the query to a text or Word Merge file. You would then
need to change the data source from Access to the file. If you save the Word
main document, the next time you open it, you will be connecting to the same
data source file.

--
Duane Hookom
MS Access MVP

Thanks Duane. Again something new to get my head around.
Ill try playing with that tomorrow. For now could you
expand a little please? In my case I have a command button
in my db which uses a hyperlink address to open the MM
document, which has been created and linked to the
concatenate query. If I get your suggestion to work for
me, can I still set up an automatic link to the MM doc and
if so how? (Im not sure how I could do this with the need
to use an intermediary delimited file). Many thanks, Noel

-----Original Message-----
This sounds like a Access to Word issue rather than a problem with the
Concatenate function.
When I do mail merges, I always export the data to a delimited file. I then
set the data source of the merge within Word to the file.

--
Duane Hookom
Microsoft Access MVP

Hi again Duane. Ive tried what you advised and it still
doesn’t work. I took out Group By in my final query (the
other one doesn’t use Group By), then tried using First
instead. The result in both cases was the same, my Child
data is cut off exactly as before. One odd thing I
noticed, when I look at the results of the query in
datasheet view (whether or not I use Group By or

Click to expand...

First
or

neither), I can step through the concatenated child
records in the Student Details field and all the data is
there. Does this not suggest that the query and Concat
program is working OK and that somehow the problem is
something to do with Access not being able to pass all
this info to the MM doc? Or does the datasheet view not
show the actual results of the query running? Any further
help would be appreciated. I now have a small sample
database I build here at home which illustrates the
problem. Would it help if you have a look at it? Thanks
again, Noel

-----Original Message-----
Thanks Duane. Hmmmmm thats given me some hope at least.
Ill have a play around with that info and will get back
to
you - might be Tuesday of next week though. Cheers, Noel
-----Original Message-----
You can't ever get more than 255 characters in a
field/column/expression
that you are using with Group By. Either change the
Group
By to First or
redo you query so that you don't group by.

--
Duane Hookom
MS Access MVP

message
Thanks for replying. Ive now proved that Im hitting
some
sort of limit, presumably the 255 chars that Michel
mentions.If I have up to say 8 or 9 child records its
OK
but over that its not safe to assume all record details
will appear. Otherwise the Concat program works fine.
Anyway, Ive decided to post you two SQL listings. The
first one is from the first query, the one that bunches
the student name, subject and yeargroup together with
tab
spacing and names this Student. The second is

Click to expand...

from
the

query that runs the Concatenate program on this Student
grouping and calls the result Student Details. In his
post, Michel seems to be suggesting that I change
Student
Details to a Memo but its not a real field in a table
so
Im confused. Ill ask him to clarify. Thanks again for
your
help.

First SQL:

SELECT [Schools and Offers].*, Placements.*, [Students
Master].*, Placements.PlacementStage, Chr$(9) &
[Students
Master].[FirstName] & " " & [Surname] & Chr(9) &
[Students
Master].[Subject] & Chr(9) & [Placements]. [YearGroup]
AS
Student, [Schools and Offers].FirstName, [Students
Master].Course, [Students Master].CourseYear, [Students
Master].Archive, [Students Master].Status,
Placements.Result, [Students Master].Subject, [Schools
and
Offers].SchoolName, Placements.[Notes- Trans&Accom],
[Rolle
Tutors].RolleTutorFirstName, [Rolle
Tutors].RolleTutorSecondName, Mentors.MentorFirstName,
Mentors.MentorSecondName, Placements.Status, [Rolle
Tutors].RolleTutorID, Mentors.MentorID,
Placements.PairedTutor
FROM [Students Master] INNER JOIN ([Schools and Offers]
INNER JOIN ([Rolle Tutors] RIGHT JOIN (Mentors RIGHT
JOIN
Placements ON Mentors.MentorID = Placements.MentorID)
ON
[Rolle Tutors].RolleTutorID = Placements.RolleTutorID)
ON
[Schools and Offers].SchoolID = Placements.SchoolID) ON
[Students Master].StudentID = Placements.StudentID
WHERE (((Placements.PlacementStage)="SBW") AND
(([Students
Master].Course)="BEd") AND (([Students
Master].CourseYear)
="Year 1") AND (([Students Master].Archive)=No) AND
(([Students Master].Status)="Live") AND
((Placements.Result) Is Null))
ORDER BY [Schools and Offers].SchoolName;

Second SQL:

SELECT [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select

Click to expand...

Placements
BEd

Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW].
[Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1
SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select

Click to expand...

Placements
BEd

Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers]. [SchoolID],Chr
(13)
&
Chr(10)) AS [Student Details], Format(Date(),"dd mmmm
yyyy") AS [Date]
FROM [Select Placements BEd Yr1 SBW]
GROUP BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1 SBW].
[Schools and Offers].FirstName, [Select

Click to expand...

Placements
BEd

Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW].
[Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1
SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select

Click to expand...

Placements
BEd

Yr1
SBW].PostCode, Concatenate("SELECT Student FROM [Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers]. [SchoolID],Chr
(13)
&
Chr(10)), Format(Date(),"dd mmmm yyyy")
ORDER BY [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolName;

-----Original Message-----
Your query that contains the SchoolID should

Click to expand...

have
only

distinct records for
each school. Please post the full SQL of your query
that
uses the
Concatenate() function.

--
Duane Hookom
MS Access MVP

"Noel" <[email protected]>

Click to expand...

wrote
in

message
Hi. Can Duane or anyone else help here please? I
have
used
Duanes Concatenate program to allow me to group
together
multiple child records in a Mail Merge. Everything
works
well but now I have found that, with more than a
certain
number of child records, the merge just stops mid
record.
It seems to be related somehow to the number of
characters
in the block of child records (around 219, ignoring
Tabs)
but as these dont come from an actual field, I cant
see
why it has a limit. For info, I have two queries.
The
first builds a Student record grouping and spaces
them
with Tabs as follows

Student: Chr$(9) & [Students Master]. [FirstName]
& " " &
[Surname] & Chr(9) & [Students Master].

Click to expand...

[Subject]
&

Chr
(9)
& [Placements].[YearGroup]

Then in a second, the Concatenate program is called
up
to
allow the child records to be grouped and presented
in
the
Mail Merge document as field name Student Details as
follows

Student Details: Concatenate("SELECT Student FROM
[Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers]. [SchoolID],Chr
(13)
&
Chr(10))

As I say, Student and Student Details arent actual
fields
in a table, so dont have a set size. The queries
show
all
the child records in full so why do they get
chopped
off
when grouped in the MMerge. I cant see any sizing
to
be
done in the MMerge doc and it doesnt help if I
provide
lots of space around the Student Details

Click to expand...

field.
Any

ideas?
Thanks, Noel

.

.

.

.

Click to expand...

Click to expand...

.

Noel · Oct 26, 2003

Hi Duane, Hopefully I caught you before you read the last
post. I think Ive actually got this to work. I used your
Concat program on my indivdual fields (rather than
grouping them together first, so that ordinary text acn
preceed it) then merged the query to a MM doc. In the doc
I place the cursor where the student related fields have
to go and set up three columns, from that point onwards.
Then I insert the Student Name field and immediately
insert a column break, then I insert the Subject field and
another column break etc. After the last field I set up
one column from that point onwards, so that normal text
can follow. And it works! I now have to set this up in my
office Db but hopefully there wont be any twists to the
tail of this one. For now, thanks for all your time Duane.
Hopefully this is will be all you hear from me on this
one. Cheers, Noel

-----Original Message-----
Thanks Duane. Ive just tried your last suggestion and Im
afraid it doesnt work. The data exported to the delimited
file is exactly the same as that shown on the MM docs - it
cuts off midway through one of the child records - it
looks like the datasheet view of the Concat query isnt
really showing the true results of the query running and
the problem (or limitation) is within the query. Can you
offer any further advice? Surely Im not the only person
who wants a significant amount of Child record data to
come out in a MM doc?

Perhaps a different approach would work. What do you think
of my way of preparing the Student Details by linking
Name, then Subject then YearGroup, separated by tabs and
ending with carriage return then passing this as one field
to your Concat program? This is the only way I can think
of getting the MM doc to show the relevant child record
fields one above another. Is there another way of doing
this that may avoid the problem Im getting, cos doing it
my way, the MM is having to deal with one big field. Is
there a way of getting the child record fields to line up
in columns if I were to offer separate fields for
StudentName, Subject etc to your Concat program? That way
each separate field would have its own 255 Chr limitation
which would not be a problem. Have we run dry on the Group
By/First theory? Thanks again for your time, Noel

-----Original Message-----
I just save/export the query to a text or Word Merge file. You would then
need to change the data source from Access to the file. If you save the Word
main document, the next time you open it, you will be connecting to the same
data source file.

--
Duane Hookom
MS Access MVP

Thanks Duane. Again something new to get my head around.
Ill try playing with that tomorrow. For now could you
expand a little please? In my case I have a command button
in my db which uses a hyperlink address to open the MM
document, which has been created and linked to the
concatenate query. If I get your suggestion to work for
me, can I still set up an automatic link to the MM doc and
if so how? (Im not sure how I could do this with the need
to use an intermediary delimited file). Many thanks, Noel

-----Original Message-----
This sounds like a Access to Word issue rather than a
problem with the
Concatenate function.
When I do mail merges, I always export the data to a
delimited file. I then
set the data source of the merge within Word to the file.

--
Duane Hookom
Microsoft Access MVP

message
Hi again Duane. Ive tried what you advised and it still
doesn’t work. I took out Group By in my final query (the
other one doesn’t use Group By), then tried using First
instead. The result in both cases was the same, my
Child
data is cut off exactly as before. One odd thing I
noticed, when I look at the results of the query in
datasheet view (whether or not I use Group By or First
or
neither), I can step through the concatenated child
records in the Student Details field and all the data is
there. Does this not suggest that the query and Concat
program is working OK and that somehow the problem is
something to do with Access not being able to pass all
this info to the MM doc? Or does the datasheet view not
show the actual results of the query running? Any
further
help would be appreciated. I now have a small sample
database I build here at home which illustrates the
problem. Would it help if you have a look at it? Thanks
again, Noel

-----Original Message-----
Thanks Duane. Hmmmmm thats given me some hope at least.
Ill have a play around with that info and will get back
to
you - might be Tuesday of next week though. Cheers,
Noel
-----Original Message-----
You can't ever get more than 255 characters in a
field/column/expression
that you are using with Group By. Either change the
Group
By to First or
redo you query so that you don't group by.

--
Duane Hookom
MS Access MVP

in

message
Thanks for replying. Ive now proved that Im hitting
some
sort of limit, presumably the 255 chars that Michel
mentions.If I have up to say 8 or 9 child records
its
OK
but over that its not safe to assume all record
details
will appear. Otherwise the Concat program works
fine.
Anyway, Ive decided to post you two SQL listings.
The
first one is from the first query, the one that
bunches
the student name, subject and yeargroup together
with
tab
spacing and names this Student. The second is from
the
query that runs the Concatenate program on this
Student
grouping and calls the result Student Details. In
his
post, Michel seems to be suggesting that I change
Student
Details to a Memo but its not a real field in a
table
so
Im confused. Ill ask him to clarify. Thanks again
for
your
help.

First SQL:

SELECT [Schools and Offers].*, Placements.*,
[Students
Master].*, Placements.PlacementStage, Chr$(9) &
[Students
Master].[FirstName] & " " & [Surname] & Chr(9) &
[Students
Master].[Subject] & Chr(9) & [Placements].
[YearGroup]
AS
Student, [Schools and Offers].FirstName, [Students
Master].Course, [Students Master].CourseYear,
[Students
Master].Archive, [Students Master].Status,
Placements.Result, [Students Master].Subject,
[Schools
and
Offers].SchoolName, Placements.[Notes- Trans&Accom],
[Rolle
Tutors].RolleTutorFirstName, [Rolle
Tutors].RolleTutorSecondName,
Mentors.MentorFirstName,
Mentors.MentorSecondName, Placements.Status, [Rolle
Tutors].RolleTutorID, Mentors.MentorID,
Placements.PairedTutor
FROM [Students Master] INNER JOIN ([Schools and
Offers]
INNER JOIN ([Rolle Tutors] RIGHT JOIN (Mentors RIGHT
JOIN
Placements ON Mentors.MentorID =
Placements.MentorID)
ON
[Rolle Tutors].RolleTutorID =
Placements.RolleTutorID)
ON
[Schools and Offers].SchoolID =
Placements.SchoolID) ON
[Students Master].StudentID = Placements.StudentID
WHERE (((Placements.PlacementStage)="SBW") AND
(([Students
Master].Course)="BEd") AND (([Students
Master].CourseYear)
="Year 1") AND (([Students Master].Archive)=No) AND
(([Students Master].Status)="Live") AND
((Placements.Result) Is Null))
ORDER BY [Schools and Offers].SchoolName;

Second SQL:

SELECT [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1
SBW].
[Schools and Offers].FirstName, [Select Placements
BEd
Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW].
[Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1
SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements
BEd
Yr1
SBW].PostCode, Concatenate("SELECT Student FROM
[Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers]. [SchoolID],Chr
(13)
&
Chr(10)) AS [Student Details], Format(Date(),"dd
mmmm
yyyy") AS [Date]
FROM [Select Placements BEd Yr1 SBW]
GROUP BY [Select Placements BEd Yr1 SBW]. [Schools
and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1
SBW].
[Schools and Offers].FirstName, [Select Placements
BEd
Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW].
[Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1
SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements
BEd
Yr1
SBW].PostCode, Concatenate("SELECT Student FROM
[Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers]. [SchoolID],Chr
(13)
&
Chr(10)), Format(Date(),"dd mmmm yyyy")
ORDER BY [Select Placements BEd Yr1 SBW]. [Schools
and
Offers].SchoolName;

-----Original Message-----
Your query that contains the SchoolID should have
only
distinct records for
each school. Please post the full SQL of your query
that
uses the
Concatenate() function.

--
Duane Hookom
MS Access MVP

in
message
Hi. Can Duane or anyone else help here please? I
have
used
Duanes Concatenate program to allow me to group
together
multiple child records in a Mail Merge.
Everything
works
well but now I have found that, with more than a
certain
number of child records, the merge just

Click to expand...

Click to expand...

stops

mid

record.
It seems to be related somehow to the number of
characters
in the block of child records (around 219,
ignoring
Tabs)
but as these dont come from an actual field, I
cant
see
why it has a limit. For info, I have two queries.
The
first builds a Student record grouping and spaces
them
with Tabs as follows

Student: Chr$(9) & [Students Master]. [FirstName]
& " " &
[Surname] & Chr(9) & [Students Master]. [Subject]
&
Chr
(9)
& [Placements].[YearGroup]

Then in a second, the Concatenate program is
called
up
to
allow the child records to be grouped and
presented
in
the
Mail Merge document as field name Student
Details as
follows

Student Details: Concatenate("SELECT Student FROM
[Select
Placements BEd Yr1 SBW] WHERE [Schools and
Offers].
[SchoolID] =" & [Schools and Offers].
[SchoolID],Chr
(13)
&
Chr(10))

As I say, Student and Student Details arent
actual
fields
in a table, so dont have a set size. The queries
show
all
the child records in full so why do they get
chopped
off
when grouped in the MMerge. I cant see any sizing
to
be
done in the MMerge doc and it doesnt help if I
provide
lots of space around the Student Details field.
Any
ideas?
Thanks, Noel

.

.

.

.

Click to expand...

.

Click to expand...

.

Click to expand...

Click to expand...

Duane Hookom · Oct 26, 2003

Glad it works for you.

--
Duane Hookom
MS Access MVP

Noel said:
Hi Duane, Hopefully I caught you before you read the last
post. I think Ive actually got this to work. I used your
Concat program on my indivdual fields (rather than
grouping them together first, so that ordinary text acn
preceed it) then merged the query to a MM doc. In the doc
I place the cursor where the student related fields have
to go and set up three columns, from that point onwards.
Then I insert the Student Name field and immediately
insert a column break, then I insert the Subject field and
another column break etc. After the last field I set up
one column from that point onwards, so that normal text
can follow. And it works! I now have to set this up in my
office Db but hopefully there wont be any twists to the
tail of this one. For now, thanks for all your time Duane.
Hopefully this is will be all you hear from me on this
one. Cheers, Noel

-----Original Message-----
Thanks Duane. Ive just tried your last suggestion and Im
afraid it doesnt work. The data exported to the delimited
file is exactly the same as that shown on the MM docs - it
cuts off midway through one of the child records - it
looks like the datasheet view of the Concat query isnt
really showing the true results of the query running and
the problem (or limitation) is within the query. Can you
offer any further advice? Surely Im not the only person
who wants a significant amount of Child record data to
come out in a MM doc?

Perhaps a different approach would work. What do you think
of my way of preparing the Student Details by linking
Name, then Subject then YearGroup, separated by tabs and
ending with carriage return then passing this as one field
to your Concat program? This is the only way I can think
of getting the MM doc to show the relevant child record
fields one above another. Is there another way of doing
this that may avoid the problem Im getting, cos doing it
my way, the MM is having to deal with one big field. Is
there a way of getting the child record fields to line up
in columns if I were to offer separate fields for
StudentName, Subject etc to your Concat program? That way
each separate field would have its own 255 Chr limitation
which would not be a problem. Have we run dry on the Group
By/First theory? Thanks again for your time, Noel

-----Original Message-----
I just save/export the query to a text or Word Merge file. You would then
need to change the data source from Access to the file. If you save the Word
main document, the next time you open it, you will be connecting to the same
data source file.

--
Duane Hookom
MS Access MVP

Thanks Duane. Again something new to get my head around.
Ill try playing with that tomorrow. For now could you
expand a little please? In my case I have a command button
in my db which uses a hyperlink address to open the MM
document, which has been created and linked to the
concatenate query. If I get your suggestion to work for
me, can I still set up an automatic link to the MM doc and
if so how? (Im not sure how I could do this with the need
to use an intermediary delimited file). Many thanks, Noel

-----Original Message-----
This sounds like a Access to Word issue rather than a
problem with the
Concatenate function.
When I do mail merges, I always export the data to a
delimited file. I then
set the data source of the merge within Word to the file.

--
Duane Hookom
Microsoft Access MVP

message
Hi again Duane. Ive tried what you advised and it still
doesn’t work. I took out Group By in my final query (the
other one doesn’t use Group By), then tried using First
instead. The result in both cases was the same, my
Child
data is cut off exactly as before. One odd thing I
noticed, when I look at the results of the query in
datasheet view (whether or not I use Group By or First
or
neither), I can step through the concatenated child
records in the Student Details field and all the data is
there. Does this not suggest that the query and Concat
program is working OK and that somehow the problem is
something to do with Access not being able to pass all
this info to the MM doc? Or does the datasheet view not
show the actual results of the query running? Any
further
help would be appreciated. I now have a small sample
database I build here at home which illustrates the
problem. Would it help if you have a look at it? Thanks
again, Noel

-----Original Message-----
Thanks Duane. Hmmmmm thats given me some hope at least.
Ill have a play around with that info and will get back
to
you - might be Tuesday of next week though. Cheers,
Noel
-----Original Message-----
You can't ever get more than 255 characters in a
field/column/expression
that you are using with Group By. Either change the
Group
By to First or
redo you query so that you don't group by.

--
Duane Hookom
MS Access MVP

in
message
Thanks for replying. Ive now proved that Im hitting
some
sort of limit, presumably the 255 chars that Michel
mentions.If I have up to say 8 or 9 child records
its
OK
but over that its not safe to assume all record
details
will appear. Otherwise the Concat program works
fine.
Anyway, Ive decided to post you two SQL listings.
The
first one is from the first query, the one that
bunches
the student name, subject and yeargroup together
with
tab
spacing and names this Student. The second is from
the
query that runs the Concatenate program on this
Student
grouping and calls the result Student Details. In
his
post, Michel seems to be suggesting that I change
Student
Details to a Memo but its not a real field in a
table
so
Im confused. Ill ask him to clarify. Thanks again
for
your
help.

First SQL:

SELECT [Schools and Offers].*, Placements.*,
[Students
Master].*, Placements.PlacementStage, Chr$(9) &
[Students
Master].[FirstName] & " " & [Surname] & Chr(9) &
[Students
Master].[Subject] & Chr(9) & [Placements].
[YearGroup]
AS
Student, [Schools and Offers].FirstName, [Students
Master].Course, [Students Master].CourseYear,
[Students
Master].Archive, [Students Master].Status,
Placements.Result, [Students Master].Subject,
[Schools
and
Offers].SchoolName, Placements.[Notes- Trans&Accom],
[Rolle
Tutors].RolleTutorFirstName, [Rolle
Tutors].RolleTutorSecondName,
Mentors.MentorFirstName,
Mentors.MentorSecondName, Placements.Status, [Rolle
Tutors].RolleTutorID, Mentors.MentorID,
Placements.PairedTutor
FROM [Students Master] INNER JOIN ([Schools and
Offers]
INNER JOIN ([Rolle Tutors] RIGHT JOIN (Mentors RIGHT
JOIN
Placements ON Mentors.MentorID =
Placements.MentorID)
ON
[Rolle Tutors].RolleTutorID =
Placements.RolleTutorID)
ON
[Schools and Offers].SchoolID =
Placements.SchoolID) ON
[Students Master].StudentID = Placements.StudentID
WHERE (((Placements.PlacementStage)="SBW") AND
(([Students
Master].Course)="BEd") AND (([Students
Master].CourseYear)
="Year 1") AND (([Students Master].Archive)=No) AND
(([Students Master].Status)="Live") AND
((Placements.Result) Is Null))
ORDER BY [Schools and Offers].SchoolName;

Second SQL:

SELECT [Select Placements BEd Yr1 SBW].[Schools and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1
SBW].
[Schools and Offers].FirstName, [Select Placements
BEd
Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW].
[Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1
SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements
BEd
Yr1
SBW].PostCode, Concatenate("SELECT Student FROM
[Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers]. [SchoolID],Chr
(13)
&
Chr(10)) AS [Student Details], Format(Date(),"dd
mmmm
yyyy") AS [Date]
FROM [Select Placements BEd Yr1 SBW]
GROUP BY [Select Placements BEd Yr1 SBW]. [Schools
and
Offers].SchoolID, [Select Placements BEd Yr1
SBW].HeadTeacherTitle, [Select Placements BEd Yr1
SBW].
[Schools and Offers].FirstName, [Select Placements
BEd
Yr1
SBW].SecondName, [Select Placements BEd Yr1 SBW].
[Schools
and Offers].SchoolName, [Select Placements BEd Yr1
SBW].AddressLine1, [Select Placements BEd Yr1
SBW].TownLine1, [Select Placements BEd Yr1
SBW].TownLine2,
[Select Placements BEd Yr1 SBW].TownLine3, [Select
Placements BEd Yr1 SBW].County, [Select Placements
BEd
Yr1
SBW].PostCode, Concatenate("SELECT Student FROM
[Select
Placements BEd Yr1 SBW] WHERE [Schools and Offers].
[SchoolID] =" & [Schools and Offers]. [SchoolID],Chr
(13)
&
Chr(10)), Format(Date(),"dd mmmm yyyy")
ORDER BY [Select Placements BEd Yr1 SBW]. [Schools
and
Offers].SchoolName;

-----Original Message-----
Your query that contains the SchoolID should have
only
distinct records for
each school. Please post the full SQL of your query
that
uses the
Concatenate() function.

--
Duane Hookom
MS Access MVP

in
message
Hi. Can Duane or anyone else help here please? I
have
used
Duanes Concatenate program to allow me to group
together
multiple child records in a Mail Merge.
Everything
works
well but now I have found that, with more than a
certain
number of child records, the merge just

Click to expand...

stops

mid
record.
It seems to be related somehow to the number of
characters
in the block of child records (around 219,
ignoring
Tabs)
but as these dont come from an actual field, I
cant
see
why it has a limit. For info, I have two queries.
The
first builds a Student record grouping and spaces
them
with Tabs as follows

Student: Chr$(9) & [Students Master]. [FirstName]
& " " &
[Surname] & Chr(9) & [Students Master]. [Subject]
&
Chr
(9)
& [Placements].[YearGroup]

Then in a second, the Concatenate program is
called
up
to
allow the child records to be grouped and
presented
in
the
Mail Merge document as field name Student
Details as
follows

Student Details: Concatenate("SELECT Student FROM
[Select
Placements BEd Yr1 SBW] WHERE [Schools and
Offers].
[SchoolID] =" & [Schools and Offers].
[SchoolID],Chr
(13)
&
Chr(10))

As I say, Student and Student Details arent
actual
fields
in a table, so dont have a set size. The queries
show
all
the child records in full so why do they get
chopped
off
when grouped in the MMerge. I cant see any sizing
to
be
done in the MMerge doc and it doesnt help if I
provide
lots of space around the Student Details field.
Any
ideas?
Thanks, Noel

.

.

.

.

.

Click to expand...

.

Click to expand...

Click to expand...

For Duane Hookom... (concatenate fields)...	5	Dec 23, 2008
To Duane Hookom re Concatenate	8	Aug 31, 2003
Message for Duane Hookum re Concatenate	11	May 15, 2004
Message for Duane Hookom	2	Aug 16, 2003
Grouping in Mailmerge	2	Jun 30, 2003
Problems Loading Large String Array into Array variable	6	May 6, 2009

Urgent for Duane Hookom re Concatenate

Noel

Michel Walsh

Noel

Michel Walsh

Noel

Michel Walsh

Duane Hookom

Noel

Duane Hookom

Noel

Noel

Duane Hookom

Noel

Duane Hookom

Noel

Noel

Duane Hookom

Ask a Question

Similar Threads