Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    add to Module (2000 (all updates))

    I have a module which takes names from a table and displays them horizontally, as follows...
    James, Linda, Susan

    This is used in a directory where James, Linda and Susan are children in a Smith family, so the entire display would be...
    SMITH: Harold & Joan
    James, Linda, Susan

    The Module code is...
    Function Concat _
    (aRSet As String, _
    aField As String, _
    aCondition As String) As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strRes As String
    If aCondition <> "" Then
    strSQL = " AND (" & aCondition & ")"
    End If
    If strSQL <> "" Then
    strSQL = " WHERE" & Mid$(strSQL, 5)
    End If
    strSQL = "SELECT [" & aField & "] FROM [" & aRSet & "]" & strSQL
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strSQL)
    While Not rst.EOF
    strRes = strRes & ", " & rst(aField)
    rst.MoveNext
    Wend
    If strRes <> "" Then
    strRes = Mid$(strRes, 3)
    End If
    Concat = strRes
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End Function

    The Directory report has this line to reference the Module
    =Concat("tblChildren","ChildName","MemberID = " & [UniqueID])

    How do I add to this module to accomodate situations where one of the children's names is different from that of the parents?
    SMITH: Harold & Joan
    James Farnsworth, Linda, Susan

    I have tried various IIf structures but can't seem to quite get it.

    Thanks.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: add to Module (2000 (all updates))

    Create a query based on tblChildren. Add the MemberID field to the query design grid, plus a calculated field

    FullName: [ChildName] & (" "+[LastName])

    where LastName is the name of the field in tblChildren that specifies the last name if it is different from that of the parents. I have assumed that this field is empty if the child has the same surname as the parents. Save this query as qryChildren.

    Change the expression you are using to

    Concat("qryChildren","FullName","MemberID=" & [UniqueID])

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: add to Module (2000 (all updates))

    Hans
    Thanks. Works perfectly.

    I was trying to modify the Module code and was getting nowhere.

    Tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •