concatenate string with "and" before last entry

Concatenate has opened a whole new world, but I have one question:

I am using Concantenate to create a string of roles together for a letter.
For example "Thanks for serving as Role1, Role2".

Is it possible to tell the report to insert an "and" before the last role so
it would read: "Thanks for serving as Role1, and Role2". Some of our members
have served in over 15 roles, but either way the letter may not pass the
board if it doesn't have the "and".

I am not sure what information would be useful, so I will offer this at this
time:
My concatenate query:

SELECT tblCustomerAddresses.[ID Number], tblCustomerAddresses.Title,
tblCustomerAddresses.MailAddress1, tblCustomerAddresses.MailAddress2,
tblCustomerAddresses.MailCity, tblCustomerAddresses.MailState,
tblCustomerAddresses.MailZip, tblCustomerAddresses.Country,
tblCustomerAddresses.FirstName, tblCustomerAddresses.LastName,
Concatenate("SELECT SpecialRole1 FROM tblJunctionNamesRoles WHERE NameLookup
= " & [ID Number]) AS Roles
FROM tblCustomerAddresses;


Thanks ahead of time for your help, I am really amazed by the talent on this
board!

.

Relevant Pages

  • Re: Message for Duane Hookum re Concatenate
    ... Function Concatenate(pstrSQL As String, _ ... >>Doug Steele, Microsoft Access MVP ... >>> in Duanes code? ... >>>>> Concatenate code which Ive used successfully on a ...
    (microsoft.public.access.modulesdaovba)
  • Re: Excel needs to expand text concatenation capability
    ... Optional DelimitWith As String) As String ... > Optional DelimitWith As String) As String ... > Dim Cel As Range ... >> I would love to be able to concatenate any non-blank entries from a list. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: append memo fields together sequentially
    ... Did you go to the site and download the database and then extract the function and paste it into a module in your database. ... did you save the module with a name OTHER than Concatenate. ... Optional pstrDelim As String = ", ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.queries)
  • Re: Number Formatting in VBSCRIPT
    ... >>>One approach is to concatenate enough zeroes in front of the target ... >would seem to probably save many nanoseconds and is a better approach if you ... Scripts are generally ... >between numeric and string data types. ...
    (microsoft.public.scripting.vbscript)
  • Re: export of linkages between objects into MS project
    ... function that Duane Hookom posted a while ago to concatenate values from ... related records into a comma-separated string. ... 'tblFamMem with FamID, FirstName, DOB,... ... and each production activity can have many research activities ...
    (microsoft.public.access.externaldata)