[RESOLVED] Concatenating in a loop

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

Code:

---

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 ","

Code:

---

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?

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

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, "##,##")

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...

Code:

---

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.

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...

Code:

---

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

---

vb Code:

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

Quote:

---

Originally Posted by Tony Aston

vb Code:

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?

To remove the trailing comma

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

Tony

Quote:

---

Originally Posted by Tony Aston

To remove the trailing comma

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

Tony

---

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

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

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