Home
Pipeline
DataHouse
Contact us
Access Tips
Freeware
Links
|
|
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
|
