I am trying to capture a single field from multiple rows and concantenate
them into a delimited string.

Dim dbs As dao.Database
Dim rst As dao.Recordset
Dim varArray As Variant

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblPhotoNames ORDER BY
PhotoName")
varArray = rstRSet.GetRows(10000)

'I would like to write this array to a delimited string.

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Thanks.

hi,

On 01.04.2010 15:35, vek wrote:
> I am trying to capture a single field from multiple rows and concantenate
> them into a delimited string.
> Set rst = .OpenRecordset("SELECT * FROM tblPhotoNames ORDER BY PhotoName")
hmm, '*' is not a single field...

Take a look at

http://www.mvps.org/access/modules/mdl0008.htm


mfG
--> stefan <--

"vek" <(E-Mail Removed)> wrote in message
news37C20F9-A714-4563-A609-(E-Mail Removed)...
>I am trying to capture a single field from multiple rows and concantenate
> them into a delimited string.
>
> Dim dbs As dao.Database
> Dim rst As dao.Recordset
> Dim varArray As Variant
>
> Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblPhotoNames ORDER BY
> PhotoName")
> varArray = rstRSet.GetRows(10000)
>
> 'I would like to write this array to a delimited string.
>
> rst.Close
> Set rst = Nothing
> dbs.Close
> Set dbs = Nothing


Code for a function that does this is posted at:

http://www.mvps.org/access/modules/mdl0004.htm
Modules: Return a concatenated list of sub-record values

Although you want something a little simpler than that, you can adapt the
technique.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Hi,
you can use Join():

str=join(varArray ,",")

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


"vek" <(E-Mail Removed)> wrote in message
news37C20F9-A714-4563-A609-(E-Mail Removed)...
> I am trying to capture a single field from multiple rows and concantenate
> them into a delimited string.
>
> Dim dbs As dao.Database
> Dim rst As dao.Recordset
> Dim varArray As Variant
>
> Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblPhotoNames ORDER BY
> PhotoName")
> varArray = rstRSet.GetRows(10000)
>
> 'I would like to write this array to a delimited string.
>
> rst.Close
> Set rst = Nothing
> dbs.Close
> Set dbs = Nothing
>
> Thanks.

Vek -

What field do you want to use? I would suggest only pulling that field
(rather than *) from the table into the recordset. You also need to correct
the recordset name or your code won't work. You should have Option Explicit
at the top of all your modules so that compiling will help find these issues.
If you pull * from tblPhotoNames, then you will need to provide the GetRows
statement with the field to pull. Look up help on these functions.

This is untested, but something to start from.

Dim dbs As dao.Database
Dim rst As dao.Recordset
Dim varArray As Variant
Dim strNewString as String
Dim varI as Variant

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblPhotoNames ORDER BY
PhotoName")
varArray = rst.GetRows(10000)

For each varI in varArray
strNewString = strNewString & varArray(I) & ";"
Next
strNewString = Left(strNewString,len(strNewString)-1) 'remove final
semicolon

rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--
Daryl S


"vek" wrote:

> I am trying to capture a single field from multiple rows and concantenate
> them into a delimited string.
>
> Dim dbs As dao.Database
> Dim rst As dao.Recordset
> Dim varArray As Variant
>
> Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblPhotoNames ORDER BY
> PhotoName")
> varArray = rstRSet.GetRows(10000)
>
> 'I would like to write this array to a delimited string.
>
> rst.Close
> Set rst = Nothing
> dbs.Close
> Set dbs = Nothing
>
> Thanks.

Have you looked at the VBA Join function? That would appear to be what you
want although it only works on one-dimensional arrays. That should work if
you are returning only ONE field.

Do you want all 10,000 rows in one really long string?
Or are you trying to generate 10,000 separate strings into a file?

Do you want a comma delimited string?

Are you attempting to put these all into a text file?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

vek wrote:
> I am trying to capture a single field from multiple rows and concantenate
> them into a delimited string.
>
> Dim dbs As dao.Database
> Dim rst As dao.Recordset
> Dim varArray As Variant
>
> Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblPhotoNames ORDER BY
> PhotoName")
> varArray = rstRSet.GetRows(10000)
>
> 'I would like to write this array to a delimited string.
>
> rst.Close
> Set rst = Nothing
> dbs.Close
> Set dbs = Nothing
>
> Thanks.

Dirk-

Thanks for the good advise. Part of the function that you specified includes:
Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

My tables use 'ReplicationID' for the primary fields. How can I modify this
code to use them.

Thanks again.


"Dirk Goldgar" wrote:

>
> Code for a function that does this is posted at:
>
> http://www.mvps.org/access/modules/mdl0004.htm
> Modules: Return a concatenated list of sub-record values
>
> Although you want something a little simpler than that, you can adapt the
> technique.
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>

John-

I am trying to fill a memo field with a comma delimited string that
represents one field of the 'many' table in a 'one' to 'many' relationship.
The primary ID's are Replication ID. I would like to end up with a two field
table. One field is the primary key from the 'one' table and a memo field
that contains the concantenated and delimited data from multiple rows of the
'many' table.

I like the idea of a function in a query.

Thanks much.



"John Spencer" wrote:

> Have you looked at the VBA Join function? That would appear to be what you
> want although it only works on one-dimensional arrays. That should work if
> you are returning only ONE field.
>
> Do you want all 10,000 rows in one really long string?
> Or are you trying to generate 10,000 separate strings into a file?
>
> Do you want a comma delimited string?
>
> Are you attempting to put these all into a text file?
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> vek wrote:
> > I am trying to capture a single field from multiple rows and concantenate
> > them into a delimited string.
> >
> > Dim dbs As dao.Database
> > Dim rst As dao.Recordset
> > Dim varArray As Variant
> >
> > Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblPhotoNames ORDER BY
> > PhotoName")
> > varArray = rstRSet.GetRows(10000)
> >
> > 'I would like to write this array to a delimited string.
> >
> > rst.Close
> > Set rst = Nothing
> > dbs.Close
> > Set dbs = Nothing
> >
> > Thanks.
> .
>

"vek" <(E-Mail Removed)> wrote in message
news:3513F417-2A35-4F46-B039-(E-Mail Removed)...
> Dirk-
>
> Thanks for the good advise. Part of the function that you specified
> includes:
> Select Case strIDType
> Case "String":
> strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
> Case "Long", "Integer", "Double": 'AutoNumber is Type Long
> strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
> Case Else
> GoTo Err_fConcatChild
> End Select
>
> My tables use 'ReplicationID' for the primary fields. How can I modify
> this
> code to use them.


If you need to do that -- your original question didn't involve setting
criteria -- you might try this additional case:

Case "GUID": 'use for Replication ID or other GUID fields
strSQL = strSQL & "[" & strIDName & "] = " & _
StringFromGUID(varCustomerID)

I haven't tested that, though.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Ok, then you need some VBA to concatenate the data.

Here are links (url) to three examples.

Duane Hookom
http://www.rogersaccesslibrary.com/f...sts.asp?TID=16

Allen Browne
http://allenbrowne.com/func-concat.html

The Access Web
http://www.mvps.org/access/modules/mdl0004.htm

I'm not sure how well any of them will work with a replication id. You may
need to transform the replication id value using the StringFromGUID function.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

vek wrote:
> John-
>
> I am trying to fill a memo field with a comma delimited string that
> represents one field of the 'many' table in a 'one' to 'many' relationship.
> The primary ID's are Replication ID. I would like to end up with a two field
> table. One field is the primary key from the 'one' table and a memo field
> that contains the concantenated and delimited data from multiple rows of the
> 'many' table.
>
> I like the idea of a function in a query.
>
> Thanks much.
>
>
>
> "John Spencer" wrote:
>
>> Have you looked at the VBA Join function? That would appear to be what you
>> want although it only works on one-dimensional arrays. That should work if
>> you are returning only ONE field.
>>
>> Do you want all 10,000 rows in one really long string?
>> Or are you trying to generate 10,000 separate strings into a file?
>>
>> Do you want a comma delimited string?
>>
>> Are you attempting to put these all into a text file?
>>
>> John Spencer
>> Access MVP 2002-2005, 2007-2010
>> The Hilltop Institute
>> University of Maryland Baltimore County
>>
>> vek wrote:
>>> I am trying to capture a single field from multiple rows and concantenate
>>> them into a delimited string.
>>>
>>> Dim dbs As dao.Database
>>> Dim rst As dao.Recordset
>>> Dim varArray As Variant
>>>
>>> Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblPhotoNames ORDER BY
>>> PhotoName")
>>> varArray = rstRSet.GetRows(10000)
>>>
>>> 'I would like to write this array to a delimited string.
>>>
>>> rst.Close
>>> Set rst = Nothing
>>> dbs.Close
>>> Set dbs = Nothing
>>>
>>> Thanks.
>> .
>>