Report text box, needs commas & "and" only for the names filled in

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sarae - 27 Jan 2006 15:08 GMT

Have a table of landowners, could be 2, could be 6. Report text box needs to
recognize number of landowners and put an "and" between only 2 owners, or
commas between the 1st, 2nd, 3rd, then an "and" before the last name. I've
been trying to write expressions for this. Tried a null expression, but can
only work with 2 fields then, and I have a field for first, middle and last
name, for up to 6 landowners. Can someone help me!?

Reply to this Message

Tim Ferguson - 27 Jan 2006 17:57 GMT

> Report text box
> needs to recognize number of landowners and put an "and" between only
> 2 owners, or commas between the 1st, 2nd, 3rd, then an "and" before
> the last name.

What - no Oxford comma? Shocking...

> I've been trying to write expressions for this.

Can't imagine doing it in an expression, but it should not be too hard in
a function:

Public Function SplitUpNames(NamesArray As Variant) As String

Dim i As Integer
Dim temp As String

' mug trapping
If Not IsArray(NamesArray) Then SplitUpNames = "": Exit Function

' iterate the array
For i = 0 To UBound(NamesArray)
' treat the last one and next to last one separately
Select Case (UBound(NamesArray) - i)
' no punctuation after the last one
Case 0: temp = temp & NamesArray(i)
' and "and" after the next to last
Case 1: temp = temp & NamesArray(i) & " and "
' a comma after everything else
Case Else: temp = temp & NamesArray(i) & ", "

End Select
Next i

' return the value
SplitUpNames = temp

End Function

If it's more convenient to use in a report, you can change the input
argument to a Recordset, but the same thing would work.

Hope it helps

Tim F

Reply to this Message