Building a dynamic email with correct punctuation.

[emozley]

What would be the best way of taking a series of names from a recordset and putting ', ' between all of them apart from the last two which you would put ' and ' between?

The idea is that if you click a link to cancel a meeting it sends an email that says 'Reception need no longer make security passes for Ed Mozley, Bill Gates, Donald Duck and Joe Bloggs.'

Thanks very much

Ed

 

[DotNetGnat]

using getrows() method and dealing with the array to insert comma's and 'and'

-DNG

 

[theniteowl]

There are a lot of ways to approach it but if you have the number of records beforehand you could say:

If LastRecord Then 
  myValue = myValue & " and "
Else
  myValue = myValue & ", "
End If
myValue = myValue & theName

Stamp out, eliminate and abolish redundancy!

 

[DotNetGnat]

theniteowl...shouldnt that be...

If LastBUTONERecord Then

-DNG

 

[emozley]

Actually looks like this is going to be a bit more complicated than I first thought as the rules will be different depending on whether there's 1, 2 or more than 2 people in the recordset.

1 - "Reception need no longer make Ed Mozley a security pass." (eg no 'and' or punctuation around name).

2 - "Reception need no longer make Ed Mozley and Joe Bloggs a security pass." (eg an 'and' but no punction).

3 - "Reception need no longer make Ed Mozley, Mickey Mouse and Joe Bloggs a security pass." (eg punctuation and an 'and').

Would I have to do a Select Case TotalRecords
Case 1
...
Case 2
...
Case Els
...
End Select

or is there a more efficient method?

cheers

 

[theniteowl]

Oh I was just begin generic using LastRecord as a variable that would have been determined elsewhere depending on how things were setup.
It could as easily have been
If LastRecord = True..
or
If LastRecord = x...
or
If CurrentRecord = LastRecord...

I did not detail how to actually determine which the last record is, just that it should be tested to see if it IS the last record and use " and " rather than ", ".

I'm very general these days, specifics take more time than I have.


Stamp out, eliminate and abolish redundancy!

 

[tsdragon]

If you put the names into an array, then you can just join them with a comma, and replace the last comma (if any) by using InStrRev and replace.

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[DotNetGnat]

emozley,
still the idea of having a names array holds good for all the different variations...

-DNG

 

[emozley]

Hi,

Thanks for the suggestions - I am not too familiar with the workings of arrays other than a very basic understanding so I went for the count the records first and then insert punctuation based on that. I'm not sure if my code is the most efficient but if there are any obvious improvements that can be made that would be much appreciated!

cheers


TBL.Open "SELECT COUNT(*) FROM ExternalAttendees WHERE MeetingID=" & Request.QueryString("MeetingID"), DB
TotalAtt=TBL("Expr1000")
TBL.Close

TBL.Open "SELECT UserID, FirstName, Surname FROM ExternalAttendees LEFT JOIN ExternalNames ON ExternalAttendees.UserID=ExternalNames.VisitorID WHERE MeetingID=" & Request.QueryString("MeetingID"), DB
counter=0
Do While Not TBL.EOF
Body=Body & TBL("FirstName") & " " & TBL("Surname")
TBL.MoveNext
counter=counter+1

If counter < TotalAtt and TotalAtt>2 Then
If counter<>TotalAtt-1 Then Body = Body & ", "
End If

If counter < TotalAtt and TotalAtt=2 Then
Body = Body & " and "
End If

If counter = TotalAtt-1 Then
Body = Body & " and "
End If
Loop