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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!?
 
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.
Click to expand...

What - no Oxford comma? Shocking...
I've been trying to write expressions for this.
Click to expand...

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
 
Back
Top