To Duane Hookom re Concatenate

[Noel]

Hello again Duane, I have got your Concatenate program to
work here at home on a test database. Next week I will try
it on the real thing in work. Two questions to keep me
going;

I see in your code where you have arranged for the Child
fields to be listed on one line separated by a comma and a
space (Optional pstrDelim As String = ", ") _). I have
joined four Child fields together, in a query, under one
name “Student” and I want each set of fields to be on a
line of its own and to start one Tab in from the left.
i.e.

(tab) Fred Bloggs (tab) Science (tab) Yr1
(tab) John Smith (tab) History (tab) Yr3

To get everything to line up one above the other I think I
will also need to separate the first and second names by a
tab in the query. My join line in the Query is: Student:
[Students].[FirstName] & " " & [Surname] & " " & [Subject]
& " " & [YearGroup]. How do I introduce tab spacing in the
Query and introduce tabs and line feeds in your
Concatenate program?

Secondly, in trying out the program in a Mail Merge, all
works as expected but the Mail Merged document seems to
be treated as one whole entity when printing. I will need
to specify individual pages when printing but when I try
this, I get all pages printed, even though the Print
dialogue box lets me specify say page 1, or odd pages
only. This happens when I try either Merge To Print or
Merge to a Document and then print. What am I doing wrong?
This last point will be a real show stopper if I don’t get
it sorted. Most letters will be two pages and the first
will be on headed paper, so I need to print them as two
lots – odd pages on headed then even pages on plain.

Thanks for your help. Regards, Noel

 

[Douglas J. Steele]

I'm not Duane, but the answer to the first question is to provide a value
for pstrDelim when you call the function. Pass Chr$(9) to get a tab.

 

[Noel]

Thanks Doug. I will have a play with this. I presume I can
use Chr$(9) in a Query and in the Concatenate program? I
will try anyway. The other part of the question was about
introducing a line feed in the Concatenate program. Thanks
again, Noel

-----Original Message-----
I'm not Duane, but the answer to the first question is to provide a value
for pstrDelim when you call the function. Pass Chr$(9) to get a tab.

--
Doug Steele, Microsoft Access MVP

Hello again Duane, I have got your Concatenate program to
work here at home on a test database. Next week I will try
it on the real thing in work. Two questions to keep me
going;

I see in your code where you have arranged for the Child
fields to be listed on one line separated by a comma and a
space (Optional pstrDelim As String = ", ") _). I have
joined four Child fields together, in a query, under one
name “Student” and I want each set of fields to be on a
line of its own and to start one Tab in from the left.
i.e.

(tab) Fred Bloggs (tab) Science (tab) Yr1
(tab) John Smith (tab) History (tab) Yr3

To get everything to line up one above the other I think I
will also need to separate the first and second names by a
tab in the query. My join line in the Query is: Student:
[Students].[FirstName] & " " & [Surname] & " " & [Subject]
& " " & [YearGroup]. How do I introduce tab spacing in the
Query and introduce tabs and line feeds in your
Concatenate program?

Secondly, in trying out the program in a Mail Merge, all
works as expected but the Mail Merged document seems to
be treated as one whole entity when printing. I will need
to specify individual pages when printing but when I try
this, I get all pages printed, even though the Print
dialogue box lets me specify say page 1, or odd pages
only. This happens when I try either Merge To Print or
Merge to a Document and then print. What am I doing wrong?
This last point will be a real show stopper if I don’t get
it sorted. Most letters will be two pages and the first
will be on headed paper, so I need to print them as two
lots – odd pages on headed then even pages on plain.

Thanks for your help. Regards, Noel

.

 

[Noel]

Thanks Doug. I can get Chr$(9) to work in my Query, but do
not know how to introduce these character codes in Duanes
program. I assumed (wrongly probably) that I need to put
them in place of the ", " in the following section of the
Concatenate program. But this results in the failure of
the Mail Merge sequence. Any ideas how I change the code
to seperate concatenated fields with a line feed and tab
instead of a comma and space? Thanks again for your time.
Cheers, Noel

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ")
_
As String

-----Original Message-----
Line feed is Chr$(13) & Chr$(10) in that order, although you might get away
with just Chr$(13) in Word.

--
Doug Steele, Microsoft Access MVP

Thanks Doug. I will have a play with this. I presume I can
use Chr$(9) in a Query and in the Concatenate program? I
will try anyway. The other part of the question was about
introducing a line feed in the Concatenate program. Thanks
again, Noel

-----Original Message-----
I'm not Duane, but the answer to the first question is

to
provide a value

for pstrDelim when you call the function. Pass Chr$(9)

to
get a tab.

--
Doug Steele, Microsoft Access MVP



Hello again Duane, I have got your Concatenate

program
to

work here at home on a test database. Next week I

will
try

it on the real thing in work. Two questions to keep me
going;

I see in your code where you have arranged for the Child
fields to be listed on one line separated by a comma and a
space (Optional pstrDelim As String = ", ") _). I have
joined four Child fields together, in a query, under one
name "Student" and I want each set of fields to be

on
a

line of its own and to start one Tab in from the left.
i.e.

(tab) Fred Bloggs (tab) Science (tab) Yr1
(tab) John Smith (tab) History (tab) Yr3

To get everything to line up one above the other I think I
will also need to separate the first and second names by a
tab in the query. My join line in the Query is: Student:
[Students].[FirstName] & " " & [Surname] & " " & [Subject]
& " " & [YearGroup]. How do I introduce tab spacing

in
the

Query and introduce tabs and line feeds in your
Concatenate program?

Secondly, in trying out the program in a Mail Merge, all
works as expected but the Mail Merged document seems to
be treated as one whole entity when printing. I will need
to specify individual pages when printing but when I try
this, I get all pages printed, even though the Print
dialogue box lets me specify say page 1, or odd pages
only. This happens when I try either Merge To Print or
Merge to a Document and then print. What am I doing wrong?
This last point will be a real show stopper if I

don't
get

it sorted. Most letters will be two pages and the first
will be on headed paper, so I need to print them as two
lots - odd pages on headed then even pages on plain.

Thanks for your help. Regards, Noel




.

.

 

[Douglas J. Steele]

Pass them as parameters when you call the function.

Concatenate(strSQL, Chr(13) & Chr(10))

or

Concatenate(strSQL, Chr(9))

The second parameter in the function is optional. If you don't supply
anything, it'll use the default (", "). If you supply something, it'll use
that instead.

If that doesn't work, post Duane's entire function: I don't have a copy of
it.

 

[Noel]

Thanks for this Doug but I cannot get it to work. I tried
replacing "pstrSQL As String," first with "pstrSQL, Chr
(13) & Chr(10)" then with "strSQL, Chr(13) & Chr(10)" but
the code turns red when I save it and I get message "Word
was unable to open the data source" when I try to
MailMerge. Here is Duanes full code (although I had to
remove a Len statement to get it to work - I had to do
this in other code too. Something to do with the Access
version? Mine is 97.) Thanks again for your help. Noel

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

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

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String
'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat &
_
.Fields(0) &
pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
Concatenate = strConcat
End Function
'End Code

 

[Douglas J. Steele]

You don't change Duane's code. You change how you call his code.

In his comments, Duane has the sample

SELECT FamID,
Concatenate("SELECT FirstName FROM tblFamMem
WHERE FamID =" & [FamID]) as FirstNames
FROM tblFamily

to produce

John, Mary, Susan

To have it produce

John
Mary
Susan

instead, you'd use:

SELECT FamID,
Concatenate("SELECT FirstName FROM tblFamMem
WHERE FamID =" & [FamID], Chr(13) & Chr(10)) as FirstNames
FROM tblFamily

To have it produce

John<t>Mary<t>Susan

(where <t> is a tab), you'd use

SELECT FamID,
Concatenate("SELECT FirstName FROM tblFamMem
WHERE FamID =" & [FamID], Chr(9)) as FirstNames
FROM tblFamily

To have it produce

John; Mary;Susan

you'd use

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


Someplace in your code, you must be calling the Concatenate function,
currently passing it an SQL string. Change that call by adding a second
parameter.

--
Doug Steele, Microsoft Access MVP

Thanks for this Doug but I cannot get it to work. I tried
replacing "pstrSQL As String," first with "pstrSQL, Chr
(13) & Chr(10)" then with "strSQL, Chr(13) & Chr(10)" but
the code turns red when I save it and I get message "Word
was unable to open the data source" when I try to
MailMerge. Here is Duanes full code (although I had to
remove a Len statement to get it to work - I had to do
this in other code too. Something to do with the Access
version? Mine is 97.) Thanks again for your help. Noel

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

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

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String
'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat &
_
.Fields(0) &
pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
Concatenate = strConcat
End Function
'End Code

-----Original Message-----
Pass them as parameters when you call the function.

Concatenate(strSQL, Chr(13) & Chr(10))

or

Concatenate(strSQL, Chr(9))

The second parameter in the function is optional. If you don't supply
anything, it'll use the default (", "). If you supply something, it'll use
that instead.

If that doesn't work, post Duane's entire function: I don't have a copy of
it.

--
Doug Steele, Microsoft Access MVP






.

 

[Noel]

Hi Doug. That got it bang on the nail - it works a treat
now. I appreciate the time you have taken to help me out.
Thank you very much. Cheers, Noel

-----Original Message-----
You don't change Duane's code. You change how you call his code.

In his comments, Duane has the sample

SELECT FamID,
Concatenate("SELECT FirstName FROM tblFamMem
WHERE FamID =" & [FamID]) as FirstNames
FROM tblFamily

to produce

John, Mary, Susan

To have it produce

John
Mary
Susan

instead, you'd use:

SELECT FamID,
Concatenate("SELECT FirstName FROM tblFamMem
WHERE FamID =" & [FamID], Chr(13) & Chr(10)) as FirstNames
FROM tblFamily

To have it produce

John<t>Mary<t>Susan

(where <t> is a tab), you'd use

SELECT FamID,
Concatenate("SELECT FirstName FROM tblFamMem
WHERE FamID =" & [FamID], Chr(9)) as FirstNames
FROM tblFamily

To have it produce

John; Mary;Susan

you'd use

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


Someplace in your code, you must be calling the Concatenate function,
currently passing it an SQL string. Change that call by adding a second
parameter.

--
Doug Steele, Microsoft Access MVP

Thanks for this Doug but I cannot get it to work. I tried
replacing "pstrSQL As String," first with "pstrSQL, Chr
(13) & Chr(10)" then with "strSQL, Chr(13) & Chr(10)" but
the code turns red when I save it and I get message "Word
was unable to open the data source" when I try to
MailMerge. Here is Duanes full code (although I had to
remove a Len statement to get it to work - I had to do
this in other code too. Something to do with the Access
version? Mine is 97.) Thanks again for your help. Noel

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

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

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String
'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat &
_
.Fields(0) &
pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
Concatenate = strConcat
End Function
'End Code

-----Original Message-----
Pass them as parameters when you call the function.

Concatenate(strSQL, Chr(13) & Chr(10))

or

Concatenate(strSQL, Chr(9))

The second parameter in the function is optional. If

you
don't supply

anything, it'll use the default (", "). If you supply something, it'll use
that instead.

If that doesn't work, post Duane's entire function: I don't have a copy of
it.

--
Doug Steele, Microsoft Access MVP



Thanks Doug. I can get Chr$(9) to work in my Query,

but
do

not know how to introduce these character codes in Duanes
program. I assumed (wrongly probably) that I need to put
them in place of the ", " in the following section of the
Concatenate program. But this results in the failure of
the Mail Merge sequence. Any ideas how I change the code
to seperate concatenated fields with a line feed and tab
instead of a comma and space? Thanks again for your time.
Cheers, Noel

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ")
_
As String




.

.

 

[Noel]

Doug, please ignore my last post re Tabs. I have sortd
this now. Thanks again for all your help. Cheers, Noel

-----Original Message-----
You don't change Duane's code. You change how you call his code.

In his comments, Duane has the sample

SELECT FamID,
Concatenate("SELECT FirstName FROM tblFamMem
WHERE FamID =" & [FamID]) as FirstNames
FROM tblFamily

to produce

John, Mary, Susan

To have it produce

John
Mary
Susan

instead, you'd use:

SELECT FamID,
Concatenate("SELECT FirstName FROM tblFamMem
WHERE FamID =" & [FamID], Chr(13) & Chr(10)) as FirstNames
FROM tblFamily

To have it produce

John<t>Mary<t>Susan

(where <t> is a tab), you'd use

SELECT FamID,
Concatenate("SELECT FirstName FROM tblFamMem
WHERE FamID =" & [FamID], Chr(9)) as FirstNames
FROM tblFamily

To have it produce

John; Mary;Susan

you'd use

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


Someplace in your code, you must be calling the Concatenate function,
currently passing it an SQL string. Change that call by adding a second
parameter.

--
Doug Steele, Microsoft Access MVP

Thanks for this Doug but I cannot get it to work. I tried
replacing "pstrSQL As String," first with "pstrSQL, Chr
(13) & Chr(10)" then with "strSQL, Chr(13) & Chr(10)" but
the code turns red when I save it and I get message "Word
was unable to open the data source" when I try to
MailMerge. Here is Duanes full code (although I had to
remove a Len statement to get it to work - I had to do
this in other code too. Something to do with the Access
version? Mine is 97.) Thanks again for your help. Noel

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

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

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String
'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat &
_
.Fields(0) &
pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
Concatenate = strConcat
End Function
'End Code

-----Original Message-----
Pass them as parameters when you call the function.

Concatenate(strSQL, Chr(13) & Chr(10))

or

Concatenate(strSQL, Chr(9))

The second parameter in the function is optional. If

you
don't supply

anything, it'll use the default (", "). If you supply something, it'll use
that instead.

If that doesn't work, post Duane's entire function: I don't have a copy of
it.

--
Doug Steele, Microsoft Access MVP



Thanks Doug. I can get Chr$(9) to work in my Query,

but
do

not know how to introduce these character codes in Duanes
program. I assumed (wrongly probably) that I need to put
them in place of the ", " in the following section of the
Concatenate program. But this results in the failure of
the Mail Merge sequence. Any ideas how I change the code
to seperate concatenated fields with a line feed and tab
instead of a comma and space? Thanks again for your time.
Cheers, Noel

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ")
_
As String




.

.