Copy of Message to Duane Hookom

[Nel]

Duane - somehow this got stuck in the Getting Started
section by mistake - I thought I had posted it here. Cheers

Hi Duane, I sent a post earlier today to the Access
General Questions newsgroup (copied below) and then, while
searching through old posts, found one of yours which
might help me. I cannot see the original post but your
reply said something like 'Word does allow you to group
records together using special codes' and suggests using
Help for more info. I have searched through Word Help
(Word 97) and cannot find anything relating to this. Does
your earlier reply relate to my question and if so, can
help me find the information I need please? Thanks, Noel

Hi, I had a long discussion about this before on this
Newsgroup and the outcome was that Access cannot do what I
want. This has continued to bug me – perhaps I did
not
explain myself too well, so here is another try. I simply
want to set up a Mail Merge based on a query that selects
a number of records from One-to-Many related tables. I
want the Merged document to reflect this relationship. To
give a simple example, say the Query selects a number of
Customer order related records. One field is the Customer
name, the other is the items ordered. Some customers will
order only one item but many will order more than one.
When I set up the Mail Merge on this query, I get one
document per ordered item. So if a customer orders ten
items (ten records produced by the query) then I get ten
Mail Merged documents, each with the same customer name
and each with a different ordered item. What I want is
just one Merged Document per Customer, showing all of the
items ordered by that customer. This seems such a simple
thing – surely Access can do this? Is there
something I
need to do in the Query or in the Mail Merged document?
Thanks, Noel

..

 

[Noel]

Hello again Duane. I’ve found your group on Google and
what I see is a Newsgroup with some postings referring to
concatenate. Is this where you expected me to go? I felt
somehow that I would end up at some sort of description of
what one posting refers to as Your Concatenate Program. I
fear that I am not clever enough to interpret these posts
and work out how to apply the code to my use. For example,
where do put the code? I am trying to produce Mail Merge
letters based on the results of a query. I can view the
SQL code for the query, which is already quite long and
complex (to my mind). Do I add your code to this SQL code
somehow? Will this allow me to have one letter with
address fields at the top (the Parent stuff) and a varying
number of Name record fields (the Child stuff) down among
the Word text? I will just want a space between the
FirstName and Surname fields of the Child records, with
each pair of fields on a new line i.e directly above one
another. Sorry to repeat myself but you mentioned NEXTIF
and if my understanding of this is correct, it definitely
does not do what I want and I am anxious not to waste your
time. If we are still on the same wavelength and you are
still willing to help, please let me know if I can supply
further info eg actual query results, field names, sample
letter layout etc. Thanks again for your help, Noel

-----Original Message-----
Thanks for this Duane. I need to take some time to
understand this but I hope to get back to you. Cheers, Noel

-----Original Message-----
Word (most versions) support the NextIf field code. From Word Help:
=========================================
Field codes: NextIf field
{ NEXTIF Expression1 Operator Expression2 }

Compares two expressions. If the comparison is true, Microsoft Word merges
the next data record into the current merge document. Merge fields that
follow the NEXTIF field in the main document are

replaced
by values from the

next data record rather than the current data record. If the comparison is
false, Word merges the next data record into a new merge document.

Don't use the NEXTIF field in the current version of Word. You can select
data records more easily by using the Mail Merge Recipients dialog box,
which appears when you connect to a data source during a mail merge.

Note NEXTIF fields cannot be used in footnotes, endnotes, annotations,
headers, footers, or data sources. The NEXTIF field cannot be nested within
any field.
=========================================

You can also concatenate multiple child records together into one value.
Search google groups on my name and concatenate. The sql you would use
places tabs between the fields like:
Concatenate("SELECT [ProductName] & chr(9) & [Qty] & chr (9) & [UnitCost] &
chr(9) & Format([Qty] * [UnitCost],'Currency') FROM tblOrderDetails WHERE
OrderID=" & [OrderID], Chr(13) & Chr(10) )
This will place tabs between fields and carriage return/line feeds between
records. I have done this in a Word text box with its

own
tab stops.

--
Duane Hookom
MS Access MVP





.

.

 

[Duane Hookom]

You should have been able to find a code/function listing.This function can
be saved in a new module and then save the module as "basConcat". You can
then create your query with a calculated column using the function. There is
a sample of how to use it in the comments of the code. As per my earlier
posting, you can use the function to return any legitimate expression.

Let me know if you can't find the full code.


--
Duane Hookom
MS Access MVP

Hello again Duane. I’ve found your group on Google and
what I see is a Newsgroup with some postings referring to
concatenate. Is this where you expected me to go? I felt
somehow that I would end up at some sort of description of
what one posting refers to as Your Concatenate Program. I
fear that I am not clever enough to interpret these posts
and work out how to apply the code to my use. For example,
where do put the code? I am trying to produce Mail Merge
letters based on the results of a query. I can view the
SQL code for the query, which is already quite long and
complex (to my mind). Do I add your code to this SQL code
somehow? Will this allow me to have one letter with
address fields at the top (the Parent stuff) and a varying
number of Name record fields (the Child stuff) down among
the Word text? I will just want a space between the
FirstName and Surname fields of the Child records, with
each pair of fields on a new line i.e directly above one
another. Sorry to repeat myself but you mentioned NEXTIF
and if my understanding of this is correct, it definitely
does not do what I want and I am anxious not to waste your
time. If we are still on the same wavelength and you are
still willing to help, please let me know if I can supply
further info eg actual query results, field names, sample
letter layout etc. Thanks again for your help, Noel

-----Original Message-----
Thanks for this Duane. I need to take some time to
understand this but I hope to get back to you. Cheers, Noel

-----Original Message-----
Word (most versions) support the NextIf field code. From Word Help:
=========================================
Field codes: NextIf field
{ NEXTIF Expression1 Operator Expression2 }

Compares two expressions. If the comparison is true, Microsoft Word merges
the next data record into the current merge document. Merge fields that
follow the NEXTIF field in the main document are

replaced
by values from the

next data record rather than the current data record. If the comparison is
false, Word merges the next data record into a new merge document.

Don't use the NEXTIF field in the current version of Word. You can select
data records more easily by using the Mail Merge Recipients dialog box,
which appears when you connect to a data source during a mail merge.

Note NEXTIF fields cannot be used in footnotes, endnotes, annotations,
headers, footers, or data sources. The NEXTIF field cannot be nested within
any field.
=========================================

You can also concatenate multiple child records together into one value.
Search google groups on my name and concatenate. The sql you would use
places tabs between the fields like:
Concatenate("SELECT [ProductName] & chr(9) & [Qty] & chr (9) & [UnitCost] &
chr(9) & Format([Qty] * [UnitCost],'Currency') FROM tblOrderDetails WHERE
OrderID=" & [OrderID], Chr(13) & Chr(10) )
This will place tabs between fields and carriage return/line feeds between
records. I have done this in a Word text box with its

own
tab stops.

--
Duane Hookom
MS Access MVP


Duane - somehow this got stuck in the Getting Started
section by mistake - I thought I had posted it here. Cheers

Hi Duane, I sent a post earlier today to the Access
General Questions newsgroup (copied below) and then, while
searching through old posts, found one of yours which
might help me. I cannot see the original post but your
reply said something like 'Word does allow you to group
records together using special codes' and suggests using
Help for more info. I have searched through Word Help
(Word 97) and cannot find anything relating to this. Does
your earlier reply relate to my question and if so, can
help me find the information I need please? Thanks, Noel

Hi, I had a long discussion about this before on this
Newsgroup and the outcome was that Access cannot do what I
want. This has continued to bug me – perhaps I did
not
explain myself too well, so here is another try. I simply
want to set up a Mail Merge based on a query that selects
a number of records from One-to-Many related tables. I
want the Merged document to reflect this relationship. To
give a simple example, say the Query selects a number of
Customer order related records. One field is the Customer
name, the other is the items ordered. Some customers will
order only one item but many will order more than one.
When I set up the Mail Merge on this query, I get one
document per ordered item. So if a customer orders ten
items (ten records produced by the query) then I get ten
Mail Merged documents, each with the same customer name
and each with a different ordered item. What I want is
just one Merged Document per Customer, showing all of the
items ordered by that customer. This seems such a simple
thing – surely Access can do this? Is there
something I
need to do in the Query or in the Mail Merged document?
Thanks, Noel

.




.

.

 

[Noel]

Thanks for taking the time to do this Duane. Time for me
to try to get it to work for my query. Prepare for further
questions! Thanks again, Noel

-----Original Message-----
15,000 huh? Guess my wife is right that I spend too much time at my
computer. Here's 15,001. Paste this code in a standard module and save it as
basConcat. Then you can use it in control sources and expressions in
queries.

'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
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
'End Code

--
Duane Hookom
MS Access MVP

Hi Duane. I have tried again, searching on your name and
then on concatenate - I get over 15000 suggestions, all
looking like postings on various Newsgroups. (I found a
post almost identical to mine). I cannot find anything
that would take me to a code listing. Can you provide a
web address? Thanks again, Noel

-----Original Message-----
You should have been able to find a code/function listing.This function can
be saved in a new module and then save the module as "basConcat". You can
then create your query with a calculated column using

the
function. There is

a sample of how to use it in the comments of the code.

As
per my earlier

posting, you can use the function to return any legitimate expression.

Let me know if you can't find the full code.


--
Duane Hookom
MS Access MVP


Hello again Duane. I've found your group on Google and
what I see is a Newsgroup with some postings

referring
to

concatenate. Is this where you expected me to go? I felt
somehow that I would end up at some sort of

description
of

what one posting refers to as Your Concatenate

Program.
I

fear that I am not clever enough to interpret these posts
and work out how to apply the code to my use. For example,
where do put the code? I am trying to produce Mail Merge
letters based on the results of a query. I can view the
SQL code for the query, which is already quite long and
complex (to my mind). Do I add your code to this SQL code
somehow? Will this allow me to have one letter with
address fields at the top (the Parent stuff) and a varying
number of Name record fields (the Child stuff) down among
the Word text? I will just want a space between the
FirstName and Surname fields of the Child records, with
each pair of fields on a new line i.e directly above one
another. Sorry to repeat myself but you mentioned NEXTIF
and if my understanding of this is correct, it definitely
does not do what I want and I am anxious not to waste your
time. If we are still on the same wavelength and you are
still willing to help, please let me know if I can supply
further info eg actual query results, field names, sample
letter layout etc. Thanks again for your help, Noel
-----Original Message-----
Thanks for this Duane. I need to take some time to
understand this but I hope to get back to you. Cheers,
Noel
-----Original Message-----
Word (most versions) support the NextIf field code. From
Word Help:
=========================================
Field codes: NextIf field
{ NEXTIF Expression1 Operator Expression2 }

Compares two expressions. If the comparison is true,
Microsoft Word merges
the next data record into the current merge document.
Merge fields that
follow the NEXTIF field in the main document are
replaced
by values from the
next data record rather than the current data

record.
If

the comparison is
false, Word merges the next data record into a new merge
document.

Don't use the NEXTIF field in the current version of
Word. You can select
data records more easily by using the Mail Merge
Recipients dialog box,
which appears when you connect to a data source during a
mail merge.

Note NEXTIF fields cannot be used in footnotes,
endnotes, annotations,
headers, footers, or data sources. The NEXTIF field
cannot be nested within
any field.
=========================================

You can also concatenate multiple child records together
into one value.
Search google groups on my name and concatenate.

The
sql

you would use
places tabs between the fields like:
Concatenate("SELECT [ProductName] & chr(9) & [Qty]

&
chr

(9) & [UnitCost] &
chr(9) & Format([Qty] * [UnitCost],'Currency') FROM
tblOrderDetails WHERE
OrderID=" & [OrderID], Chr(13) & Chr(10) )
This will place tabs between fields and carriage
return/line feeds between
records. I have done this in a Word text box with its
own
tab stops.

--
Duane Hookom
MS Access MVP


Duane - somehow this got stuck in the Getting Started
section by mistake - I thought I had posted it here.
Cheers

Hi Duane, I sent a post earlier today to the Access
General Questions newsgroup (copied below) and then,
while
searching through old posts, found one of yours which
might help me. I cannot see the original post but your
reply said something like 'Word does allow you to group
records together using special codes' and suggests
using
Help for more info. I have searched through Word Help
(Word 97) and cannot find anything relating to this.
Does
your earlier reply relate to my question and if

so,
can

help me find the information I need please? Thanks,
Noel

Hi, I had a long discussion about this before on this
Newsgroup and the outcome was that Access cannot do
what I
want. This has continued to bug me - perhaps I did
not
explain myself too well, so here is another try. I
simply
want to set up a Mail Merge based on a query that
selects
a number of records from One-to-Many related tables. I
want the Merged document to reflect this relationship.
To
give a simple example, say the Query selects a number
of
Customer order related records. One field is the
Customer
name, the other is the items ordered. Some customers
will
order only one item but many will order more than one.
When I set up the Mail Merge on this query, I get one
document per ordered item. So if a customer

orders
ten

items (ten records produced by the query) then I get
ten
Mail Merged documents, each with the same

customer
name

and each with a different ordered item. What I

want
is

just one Merged Document per Customer, showing

all
of

the
items ordered by that customer. This seems such a
simple
thing - surely Access can do this? Is there
something I
need to do in the Query or in the Mail Merged document?
Thanks, Noel

.




.

.



.

.