UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> String Concatenation in a query    
 
   
mwacardsfan
post Nov 29 2005, 01:00 PM
Post#1



Posts: 596
Joined: 19-October 04
From: Cleveland, OH


I have a table which has two fields, Animal Family and Animal Type.
need to create a query which does concatenation on animal type based on animal family and groups by animal family.... In other words, the query result should look something like this.....
ORIGINAL TABLE
Animal Family(field)....Animal Type(field)
Domestic.................Dog
Domestic.................Cat
Domestic.................fish
Domestic.................bird
Wild...................... fox
Wild..................... .lizzard
Wild...................... bear
RESULTING QUERY
Animal Family(field)...Animal Types(field)
Domestic...............Dog, Cat, fish, bird
Wild.....................fox, lizzard, bear
Any idea how to accomplish this?
Go to the top of the page
 
Larry Larsen
post Nov 29 2005, 02:18 PM
Post#2


UA Editor + Utterly Certified
Posts: 24,108
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Would this article help.. I haven't used or tried it.
Concatenate fields in same table.
<
thumbup.gif
Go to the top of the page
 
schroep
post Nov 29 2005, 03:27 PM
Post#3


UtterAccess VIP
Posts: 5,202
Joined: 21-July 05
From: Denver, Colorado [USA]


Put this code in a module:
CODE
Public Function ConCatField(pstrTable As String, pstrField As String, Optional pstrCriteria As String = "", Optional pstrOrderBy As String, Optional pstrDelimiter As String = ",") As String
  Dim rs As New ADODB.Recordset
  
  rs.Open "Select [" & pstrField & "] FROM [" & pstrTable & "]" & IIf(Len(pstrCriteria) > 0, " WHERE " & pstrCriteria, "") & IIf(Len(pstrOrderBy) > 0, " ORDER BY " & pstrOrderBy, ""), CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  If Not rs.EOF Then
    ConCatField = rs.GetString(adClipString, , "", pstrDelimiter)
    ConCatField = Left(ConCatField, Len(ConCatField) - Len(pstrDelimiter))
  End If
  rs.Close
  Set rs = Nothing
End Function

The SQL for your query will look like:
CODE
SELECT [Animal Family],ConCatField("tablename","Animal Type","[Animal Family]='" & [Animal Family] & "'","[Animal Type]") FROM tablename GROUP BY [Animal Family] ORDER BY [Animal Family]
Go to the top of the page
 
mwacardsfan
post Nov 29 2005, 05:15 PM
Post#4



Posts: 596
Joined: 19-October 04
From: Cleveland, OH


schroep, exactly what I needed! Thanks so much!!!
Go to the top of the page
 
schroep
post Nov 29 2005, 05:21 PM
Post#5


UtterAccess VIP
Posts: 5,202
Joined: 21-July 05
From: Denver, Colorado [USA]


Glad to help.
Go to the top of the page
 
ry94080
post Mar 1 2007, 01:19 PM
Post#6



Posts: 924
Joined: 27-July 05



I recieve a compile error when i attempt this???????
confused.gif
Go to the top of the page
 
schroep
post Mar 2 2007, 12:17 AM
Post#7


UtterAccess VIP
Posts: 5,202
Joined: 21-July 05
From: Denver, Colorado [USA]


On which line?
Are you using Access 2002? If not, have you added a reference to the Microsoft ActiveX Data Objects 2.1 Library?
Go to the top of the page
 
pokan
post Jun 27 2007, 07:41 PM
Post#8



Posts: 189
Joined: 10-February 00
From: CA USA


Hi Schroep,
I'm using Access 97 and I received an error also. The error is for line...
"Public Function ConCatField(pstrTable As String, pstrField As String, Optional pstrCriteria As String = "", Optional pstrOrderBy As String, Optional pstrDelimiter As String = ",") As String Dim rs As New ADODB.Recordset".
I'm don't have much experience with modules and ActiveX. How do I add a reference to MS ActiveX Data Objects 2.1 Library?
AP
Go to the top of the page
 
WildBird
post Jun 27 2007, 08:27 PM
Post#9


UtterAccess VIP
Posts: 3,277
Joined: 19-August 03
From: Perth, Australia


Peter, I will step in here, as I am waiting on files for work!
o add references, open a module up, (code window). Go to Tools, references, and you should see a list of available references. Should pick the highest number listed generally, for ActiveX Data Objects is *normally* 2.7 or higher.
Post back if any questions.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 04:28 PM