Create a string from recordset results 3

[Cloonalt]

Can someone point me to some code to loop through the results of a recordset and create a string?

For instance I have a recordset which is returning one column. There's more than one row, and I want to concatenate the rows together to make a string - Row1, Row2, Row3, etc.

Any help is appreciated.

Thanks.

 

[jebry]

Hi!

rs.MoveFirst
YourString = ""
Do Until rs.EOF = True
YourString = YourString & rs!YourField & ";"
Loop
YourString = Left(YourString, Len(YourString) - 1)


hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[PHV]

If you have an ADODB.Recordset then you may consider its GetString method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Cloonalt]

Thank you Jeff, I need a rs.movenext for it to work for me.


Set rs = db.OpenResults(strSQL)
If rs.Count > 0 Then
rs.MoveFirst
Batches = ""
Do Until rs.EOF = True
Batches = Batches & Trim(rs.Value("psindex")) & ", "
rs.MoveNext
Loop
Batches = Left(Batches, Len(Batches) - 1)
End If

 

[Cloonalt]

PHV, yes that works too!

Now I hate to ask a dumb question but..

how to I pass my string (Batches in this case) back to the calling sub?

Thanks!

 

[jebry]

Hi!

Sorry about forgetting the move next. To pass the string back to a sub do it like this:

Public Function GetMyString() As String


Set rs = db.OpenResults(strSQL)
If rs.Count > 0 Then
rs.MoveFirst
Batches = ""
Do Until rs.EOF = True
Batches = Batches & Trim(rs.Value("psindex")) & ", "
rs.MoveNext
Loop
Batches = Left(Batches, Len(Batches) - 1)
End If

Set rs = Nothing
GetMyString = Batches

End Function

Then in the calling Sub use:

MyString = GetMyString

Of course you will need all of the declarations that I left out.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Cloonalt]

Jeff,

In the calling sub I use:

GetBatches (RequestNo)
strBatches = GetBatches

On the strBatches = GetBatches line I get
an 'argument not optional' error

The GetBatches function works and produces the right result.

Thanks again

Fran

 

[JerryKlmns]

strBatches = GetBatches (RequestNo)

 

[Cloonalt]

Of course

Thank you.