Combining Records into one Text Box

[Guest]

I'm trying to figure out how to Query a Table, pulling only one field out for
the results. Instead of having a recordset result in my form, I would like
to take the individual records and combine them into a single text box, i.e.
if each record was a unique number, instead of having the following:
1
2
3
4

I would like to produce this:

1, 2, 3, 4

I'm sure this is possible, just don't have enough background in Access
coding to do it (I could in PHP with a MySQL behind it, but we don't have
that option here at work).

TIA

 

[Douglas J. Steele]

Take a look at http://www.mvps.org/access/modules/mdl0008.htm at "The Access
Web"

 

[Marshall Barton]

I'm trying to figure out how to Query a Table, pulling only one field out for
the results. Instead of having a recordset result in my form, I would like
to take the individual records and combine them into a single text box, i.e.
if each record was a unique number, instead of having the following:
1
2
3
4

I would like to produce this:

1, 2, 3, 4

I'm sure this is possible, just don't have enough background in Access
coding to do it (I could in PHP with a MySQL behind it, but we don't have
that option here at work).

You need to create a function to do that. There's a good
one at:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

 

[Guest]

Thanks Marshall, though the link is broken. But nice site with lots of stuff
to browse through.

 

[Marshall Barton]

That's odd, the link worked for me just before I posted and
it worked agin just now.

 

[Guest]

Doug,

Thanks for the website. I've got it working inthe Sample Northwind
Database, but when I import the Module over (Named: Concate), and run the
appropriate fields through, I get the following error:
Compile Error:
User Defined-Type Not Identified

Switches to the Module and highlights 'lodb As Database' in Blue and
Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant) _
As String
in yellow.

Here's the SQL view of the query I am trying to run:

SELECT [MasterDRG].[ContractGroup],
fConcatFld("MasterDRG","ContractGroup","DRG","string",[ContractGroup]) AS DRG
FROM MasterDRG
GROUP BY [MasterDRG].[ContractGroup];

Table = MasterDRG
Select Field = ContractGroup
Concate Field = DRG

I was suprised I got this far with my limited knowledge of Access.

Thanks for any additional help.

 

[Guest]

I get the following error when clicking on the link:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

/Otherdownload.asp, line 32

That's odd, the link worked for me just before I posted and
it worked agin just now.
--
Marsh
MVP [MS Access]

Thanks Marshall, though the link is broken. But nice site with lots of stuff
to browse through.

 

[Douglas J. Steele]

For some reason, I'm unable to connect to the site at the moment, but I'm
guessing that the code uses DAO (Data Access Objects) to communicate with
the database, and you're using Access 2000 or 2002. By default, Access 2000
and 2002 only have a reference set for ADO (ActiveX Data Objects).

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it.

Note that if you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. For example, to ensure that you get a DAO recordset,
you'll need to use Dim rsCurr as DAO.Recordset (to guarantee an ADO
recordset, you'd use Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

Thanks for the website. I've got it working inthe Sample Northwind
Database, but when I import the Module over (Named: Concate), and run the
appropriate fields through, I get the following error:
Compile Error:
User Defined-Type Not Identified

Switches to the Module and highlights 'lodb As Database' in Blue and
Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant) _
As String
in yellow.

Here's the SQL view of the query I am trying to run:

SELECT [MasterDRG].[ContractGroup],
fConcatFld("MasterDRG","ContractGroup","DRG","string",[ContractGroup]) AS
DRG
FROM MasterDRG
GROUP BY [MasterDRG].[ContractGroup];

Table = MasterDRG
Select Field = ContractGroup
Concate Field = DRG

I was suprised I got this far with my limited knowledge of Access.

Thanks for any additional help.

Take a look at http://www.mvps.org/access/modules/mdl0008.htm at "The
Access
Web"

 

[Marshall Barton]

I don't know how ADODB comed into it. You should be using
DAO for this function.

 

[onedaywhen]

I don't know how ADODB comed into it. You should be using
DAO for this function.

In ADO you don't need to 'create a function to do that' because an ADO
recordset already has a GetString method e.g.

CurrentProject.Connection.Execute("CREATE TABLE Sequence (seq INT NOT
NULL);")
CurrentProject.Connection.Execute("INSERT INTO Sequence VALUES (1);")
CurrentProject.Connection.Execute("INSERT INTO Sequence VALUES (2);")
CurrentProject.Connection.Execute("INSERT INTO Sequence VALUES (3);")
? CurrentProject.Connection.Execute("SELECT seq FROM
Sequence").GetString(adClipString, , , ", ")

Jamie.

--