[RESOLVED] Concatenating in a loop

[CVDpr]

I loop the recordset and add "," between the numbers.

Dim sn As String

rsTemp.MoveFirst
While Not rsTemp.EOF
    sn = sn + "," + Format$(rsTemp("SampleNumber"))       
    rsTemp.MoveNext
Wend

This = ",123,456,789" and then i do this to remove the first ","

Dim snF As String

snF = "'" & Mid$(sn, 2, Len(sn)) & "'"

This = "123,456,789".

Is there a better way to add the "," between the numbers in the loop so i dont have to use the Mid$? or what i have is ok?

[danecook21]

Another way to cut off the beginning:
Right$(sn, Len(sn) - 1)
Not really that much better

[tropix100]

This may be more appropriate........

Dim sn As String

rsTemp.MoveFirst
While Not rsTemp.EOF
sn = sn + Format$(rsTemp("SampleNumber"))
rsTemp.MoveNext
Wend

sn = format(sn, "##,##")

[dee-u]

One option you have is to put a 'flag' (a boolean variable) in your loop that it is entering it the first time though I still prefer your current method since checking the flag for each iteration of the loop would entail performance degradation specially if you will be running a very long loop...

[Merri]

Dim sn As String

rsTemp.MoveFirst
Do Until rsTemp.EOF
    If LenB(sn) Then
        sn = sn & "," & rsTemp("SampleNumber")
    Else
        sn = rsTemp("SampleNumber")
    End If
    rsTemp.MoveNext
Loop

Checking for length is blazingly fast. Concatenating strings this way is slow. However, it is very unlikely this needs to be optimized in any way: I'm only providing a slightly cleaner code here.

[dee-u]

An ADODB recordset also has a GetString method that would automatically retrieve you the recordset in a defined format just like what you want, have a search!

A sample of which is...

GetString(adClipString,-1, ",", ",", "(NULL)")

[Tony Aston]

Dim sn As String
 
rsTemp.MoveFirst
While Not rsTemp.EOF
    sn = sn + Format$(rsTemp("SampleNumber"))     + ","   
    rsTemp.MoveNext
Wend

Put the comma at the end of the line and it should help

[dee-u]

Dim sn As String
 
rsTemp.MoveFirst
While Not rsTemp.EOF
    sn = sn + Format$(rsTemp("SampleNumber"))     + ","   
    rsTemp.MoveNext
Wend

Put the comma at the end of the line and it should help

He would then have to remove the trailing comma?

[Tony Aston]

To remove the trailing comma

sn = left$(sn,len(sn)-1)

Tony

[dee-u]

To remove the trailing comma

sn = left$(sn,len(sn)-1)

Tony

You just reversed the thread starter's current method...

[Tony Aston]

Yes, you are correct. I made the comment in haste.

[leinad31]

Define the delimiter as in post #6. This is comparable to joining elements of string arrays on a given deilmiter, e.g. Join(strArray, ",")