Concatenate function written in Access 2013 Module returns duplicate values in its result

Question

Hi,

I've been trying to find a way of concatenating rows of data, grouped by a key field.  My reason for doing this is so I can then use the resultant query as a data source for a mail merge letter.  (I've had very limited success in making a directory merge with text above and below the merge work, so this is my work around.)

After some searching, I found information on creating a function via a module.  I did so and tweaked it as I wanted a line space between each concatenated value.

This is the code for the module that I have created.  The module is called ModuleCreateConcatReturn

Option Compare Database

'Concat Returns lists of items which are within a grouped field starting each item on a new line

Public Function ConcatReturn(strGroup As String, _

                       strItem As String) As String

    Static strLastGroup As String

    Static strItems As String

    If strGroup = strLastGroup Then

        strItems = strItems & Chr(13) & Chr(10) & strItem

    Else

        strLastGroup = strGroup

        strItems = strItem

    End If

    ConcatReturn = strItems

End Function

This would be good, except that some of the results are duplicated.  Below is an example using dummy data:

Firstly, the formula:  

ListofRecipients: concatreturn([accountid],[recipientsanddetails])

where [recipientsanddetails] is a concatenated field comprising of the recipient's name, address, quantity and cost.  

Now the result:

Mrs E Black, 32 Chapel Court, Their Town, Their County, TT26 4DQ has 1 magazines at a cost of £10.50

Mrs E Black, 32 Chapel Court, Their Town, Their County, TT26 4DQ has 1 magazines at a cost of £10.50

Mr M Henry, 29 Chapel Court, Their Town has 25 magazines at a cost of £134.25

Mr M Henry, 29 Chapel Court, Their Town has 25 magazines at a cost of £134.25

Mrs E Black, 32 Chapel Court, Their Town, Their County, TT26 4DQ has 1 magazines at a cost of £10.50

Mrs E Black, 32 Chapel Court, Their Town, Their County, TT26 4DQ has 1 magazines at a cost of £10.50

Mr M Henry, 29 Chapel Court, Their Town has 25 magazines at a cost of £134.25

Mr M Henry, 29 Chapel Court, Their Town has 25 magazines at a cost of £134.25

Mrs E Black, 32 Chapel Court, Their Town, Their County, TT26 4DQ has 1 magazines at a cost of £10.50

Mrs E Black, 32 Chapel Court, Their Town, Their County, TT26 4DQ has 1 magazines at a cost of £10.50

and so on...

This then repeats for each record that the query returns, so I then created a summary query, using this query result as the source, grouping by all fields; but summing the separate total and quantity fields.  The result is ok and I'm only seeing the 1 record I'm expecting; but I'm still getting some repetition in the concatenated detail, e.g.

Mr M Henry, 29 Chapel Court, Their Town has 25 magazines at a cost of £134.25

Mrs E Black, 32 Chapel Court, Their Town, Their County, TT26 4DQ has 1 magazines at a cost of £10.50

Mr M Henry, 29 Chapel Court, Their Town has 25 magazines at a cost of £134.25

Is the problem with my initial concatenation of the [recipientsanddetailsfield]?  This is the formula for it:

RecipientsAndDetails: [RecipientDetails] & " has " & [Quantity] & " magazines at a cost of " & [Cur1Symbol] & FormatNumber([TotalSubsPrice],2)

Where [RecipientDetails] is a previously concatenated field of the contact information for each recipient.

It is in a query containing all of the data I need to generate the invoice.  The concatenation formula gives the result I am looking for.  It is only when the result of this is used with ConcatReturn that I see a problem.

If anyone has any words of advice I'd be grateful.  I have virtually no VBA skills whatsoever and I'm learning as I go.

Thanks very much.

Answer 1

Can you explain a bit more what this app does and what the letters are for?

Answer 2

Thanks. I agree with you totally.

I did that and for a small number of letters it is fine (not pretty; but fine).  However, if they want to change the way the letters are worded to suit a particular occasion and the list of recipients is in the hundreds, they wouldn't be happy.

If I can persuade them that they don't need to change their letters, then I'll definitely stick to the output to RTF option. However, they were adamant that they would need to make some changes from time to time, so I fear I may be fighting a losing battle.

Thanks again for your help.  At least I can go back to them and tell them what you've recommended.

Answer 3

So output to RTF and they can then open them in Word.

Answer 4

Hi Scott,

Thank you.  I had created a report for this, however the users want to be able to tweak the letters in Word either before they merge or after, before they print them.  I'm afraid they aren't up for email as yet.  They don't all have the same email client either.  Just as a point of interest here, there are 3 users of the database.  They all have the same structure; but the data files are different in each case.  All 3 users work from their own homes.

I've tried showing the users the report's design and how they could change the body text; but none of them were at all comfortable with that and I have been instructed that the letters need to be in a Microsoft Word document.

I've also tried to automate this process by having a button which exports the generated report into a Word document (so the users don't see the report in Access) while it charges the account. If it comes to it, this will work; but the formatting isn't great.  They could then change it in Word of course, if required.

In this instance, the data I'm looking at should be for 1 account; but that account can have as many as 20 listings (although it is most common only to have 1).  The example I showed above had 2 listings only.  It just went a bit crazy!

In some cases (depending on the letter type), there may be hundreds of letters, mostly with 1 listing; but potentially, they can have multiple listings. I don't think they'd be happy editing those letters after a merge.

Would exporting to HTML give me a better result?  If so, how could I then transfer it to Word.  That would be great for single letters; but not for multiple recipients.

Thanks again Scott.

Answer 5

If you need that many listings, I would be more inclined to produce a report,output the report as HTML and then bring the report into your e-mail. If you need some help with doing that, show us the code that generates the e-mail.