Help With VBA Code

[rparker85]

Hello!

I'm using the VBA code from Allen Browne (http://allenbrowne.com/func-concat.html) to combine child name fields where the ParentID is the same. For example in my query this:
123 - John Smith - Jane Smith


123 - John Smith - Jack Smith
123 - John Smith - Joe Smith

Will be transformed to this:
123 - John Smith - Jane, Jack, Joe Smith.

What I'm looking for is to add an IF statment to this code that will change the separator if there are more than two names combined. Right now the names are being seperated by a ", ". If there are only two names combined the separator would be "and"... so Jane and Jack Smith. If more than two names are combined the seperator would be a ", " and "and". So the field would return Jane, Jack, and Joe Smith.

Is this possible? Would anyone be able to assist with this?

Thank you!!

[rpeare]

I'm assuming you're not using multivalued fields (if you are don't!)

Do While Not rs.EOF         
    If bIsMultiValue Then             
    'For multi-valued field, loop through the values             
        Set rsMV = rs(0).Value            
        Do While Not rsMV.EOF                 
            If Not IsNull(rsMV(0)) Then                     
                strOut = strOut & rsMV(0) & strSeparator                 
            End If                 
        rsMV.MoveNext            
        Loop             
        Set rsMV = Nothing         
    ElseIf Not IsNull(rs(0)) Then             
        strOut = strOut & rs(0) & strSeparator         
    End If         
    rs.MoveNext     
Loop

right before this portion of code you may be able to do this:

dim iRecCt as long

rs.movelast()
iRecCt = rs.recordcount
rs.movefirst()

Then in your code have this:

Do While Not rs.EOF         
    If bIsMultiValue Then             
    'For multi-valued field, loop through the values             
    Set rsMV = rs(0).Value             
        Do While Not rsMV.EOF                 
            If Not IsNull(rsMV(0)) Then                     
                strOut = strOut & rsMV(0) & strSeparator                 
            End If                 
            rsMV.MoveNext             
        Loop             
    Set rsMV = Nothing         
    ElseIf Not IsNull(rs(0)) Then
        if iRecCt = 2 Then
            strOut = strOUT & rs(0) & " and "
        else                  
            strOut = strOut & rs(0) & strSeparator
        endif         
    End If         
    rs.MoveNext     
Loop

Then you would have to replace this:

    lngLen = Len(strOut) - Len(strSeparator)

with

    if iRecCt = 2 then 
         lngLen = Len(strout) - 5
    else
         lngLen = Len(strOut) - Len(strSeparator)
    endif

where 5 is the length of the " and " string

NOTE, I haven't tested this but just giving you a little direction.