Array to String

  • Thread starter Thread starter vek
  • Start date Start date
V

vek

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 said:
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
Click to expand...


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.
 
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
 
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
 
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.
 
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.
 
vek said:
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.
Click to expand...


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.
 
Ok, then you need some VBA to concatenate the data.

Here are links (url) to three examples.

Duane Hookom
http://www.rogersaccesslibrary.com/forum/forum_posts.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
 
Back
Top