Data type problems when concatenating fields.

[Whizzo]

Hi all;

I'm using this great bit of code to concatenate a fields from multiple rows into a single one.

'Concat Returns lists of items which are within a grouped field
Public Function Concat(strGroup As String, _
                       strItem As String) As String
    Static strLastGroup As String
    Static strItems As String
 
    If strGroup = strLastGroup Then
        strItems = strItems & ", " & strItem
        Else
        strLastGroup = strGroup
        strItems = strItem
    End If
    Concat = strItems
End Function

I tent to call the code using:

max(concat([tbl1]![field1],[tbl2]![field2]))

The problem I'm having is that I'm getting a Data Type Mismatch whenever I'm using a Number field as strGroup and a text field as strItem. Unfortunately I can't change the design of the underlying tables, the number fields have to remain as number fields.

Is there any way of tweaking the code to make it work with mixed data types? I've tried to look up info for CStr() but there's not much useful stuff out there!

Thanks!

[Whizzo]

Got it:

Last(concat([tblCustomers]![CustID],CStr(nz([policynumber]))))

Seems to be working...

[NeoPa]

Nicely done Whizzo.

For the interest of anyone viewing this, not familiar with the Concat() function, see Combining Rows-Opposite of Union.