Microsoft Access Tips

A few of the functions we've used in DataHouse and Pipeline. Please feel free to use all and any tips, code etc in your own applications. All tips, code etc is provided without any warranty implied, use at your own risk.

Concatenate - Joining data from different fields

To concatenate is to join together two or more fields using the & character. Joining together two or more fields of the same record (that is different columns from the same row) is pretty straightforward and can be done in a query, a form or a report. Examples
    FullName = [FirstName]&' '&[SecondName]
    Like "*" & "Keyword 1" & "*" And Like "*" & "Keyword 2" & "*" 
==================
To join together several fields from different rows is trickier. We use the following function by Duane Hookum in our keywords form to join several keywords into a single text box and then add the contents to another


1. On the keywords filed in Pipeline there is a hidden text field with data source
    =Concatenate("SELECT tblKeywords.Keyword FROM tblKeywords
     WHERE (((tblKeywords.IncludeInList)=-1))
     ORDER BY tblKeywords.Keyword;")
This passes a SQL statement to the Concatenate function specifying to include all keywords from tblKeywords which have the IncludeInList flag set to true (aka -1)


2. The following function Concatenate is by Duane Hookom, Access MVP, and is taken from http://www.tek-tips.com/faqs.cfm?fid=4233 It joins together all the keywords from above. Basically it creates a recordset of the keywords we wish to include, then steps through the recordset adding a keyword each time
    Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ", ") As String

    'example
    'tblFamily with FamID as numeric primary key
    'tblFamMem with FamID, FirstName, DOB,...
    'return a comma separated list of FirstNames
    'for a FamID
    '    John, Mary, Susan
    'in a Query
    '(This SQL statement assumes FamID is numeric)
    '===================================
    'SELECT FamID,
    'Concatenate("SELECT FirstName FROM tblFamMem
    '     WHERE FamID =" & [FamID]) as FirstNames
    'FROM tblFamily
    '===================================
    '
    'If the FamID is a string then the SQL would be
    '===================================
    'SELECT FamID,
    'Concatenate("SELECT FirstName FROM tblFamMem
    '     WHERE FamID =""" & [FamID] & """") as FirstNames
    'FROM tblFamily
    '===================================


    Dim rs As New ADODB.Recordset
        rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    Dim strConcat As String 'build return string

        With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
             .Fields(0) & pstrDelim
             .MoveNext
            Loop
        End If

        .Close
        End With
        Set rs = Nothing


    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
    End If

    Concatenate = strConcat

    End Function

3. Finally in a button on the keywords form we push the contents of the (hidden) text box into a field on another open form. You can see we're concatenating fields from different forms, ASCII characters, text in quotation marks and the current Date.
    Dim strNotes As String
    strNotes = Forms!frmContact!txtContactGeneralNotes _
                & Chr(13) & Chr(10) & Chr(13) & Chr(10) _
                & "Keywords Added: " & Date & Chr(13) & Chr(10) & Me.txtKeywordList


    Forms!frmContact!txtContactGeneralNotes = strNotes

These Access tips are brought to you by DataHouseSoftware creators of Pipeline and DataHouse