Removing a trailing ;

[eckert1961]

Hello, I am using the following code which combines detail records from 1 table into another.

[COLOR=Blue]Private Sub cmdAppend_Click()
On Error GoTo Err_Handler
   Dim cnn As ADODB.Connection
   Dim rsStu As ADODB.Recordset
   Dim rsStuList As ADODB.Recordset
   Dim rsDetails As ADODB.Recordset
   Dim SQL As String
   Dim vDetails As Variant
   
   Set cnn = CurrentProject.Connection
   Set rsStu = New ADODB.Recordset
   Set rsStuList = New ADODB.Recordset
   Set rsDetails = New ADODB.Recordset
    
   ' empty table Order Receipt Table.
   SQL = "Delete * from [Order Receipt Table]"
   cnn.Execute SQL
    
   ' open recordsets.
   SQL = "Select OrderID, Firstname, Lastname, ParentsNames, OrderDate, CheckNumber, Details, TotalOfSumOfLineTotal" & _
         " from [Order Receipt Table]"
   rsStu.Open SQL, cnn, adOpenDynamic, adLockOptimistic
   
   SQL = "Select distinct OrderID, Firstname, Lastname, ParentsNames, OrderDate, CheckNumber, TotalOfSumOfLineTotal" & _
         " from [Order Payments]"
   rsStuList.Open SQL, cnn, adOpenForwardOnly, adLockReadOnly
   
   Do While Not rsStuList.EOF
     SQL = "Select Details" & _
           " from [Order Payments]" & _
           " where FirstName='" & rsStuList!FirstName & "'" & _
           " order by LastName"
     rsDetails.Open SQL, cnn, adOpenDynamic, adLockOptimistic
     
     ' combine Info.
     Do While Not rsDetails.EOF
        vDetails = vDetails & Trim(rsDetails!Details) & "; "
        rsDetails.MoveNext
     Loop
     vDetails = LTrim(vDetails)
     
     ' append to tblStudent.
     rsStu.AddNew
     rsStu!OrderID = rsStuList!OrderID
     rsStu!FirstName = rsStuList!FirstName
     rsStu!LastName = rsStuList!LastName
     rsStu!ParentsNames = rsStuList!ParentsNames
     rsStu!OrderDate = rsStuList!OrderDate
     rsStu!Checknumber = rsStuList!Checknumber
     rsStu!TotalOfSumOfLineTotal = rsStuList!TotalOfSumOfLineTotal
     If Not vDetails = "" Then
       rsStu!Details = vDetails
     End If
     rsStu.Update
     
     vDetails = Null
     rsDetails.Close
     rsStuList.MoveNext
   Loop
        
   Set rsStu = Nothing
   Set rsStuList = Nothing
   Set rsDetails = Nothing
   MsgBox "Records appended to table Order Receipt Table."
   Exit Sub
   
Err_Handler:
   MsgBox Err.Description
End Sub[/COLOR]

This results in the following output to the Details column.

Size Chord, Rising Sun - Nunchaku; Size 6 Foot, Rising Sun - Bo;

I want to remove the trailing ; so that the final output is,

Size Chord, Rising Sun - Nunchaku; Size 6 Foot, Rising Sun - Bo

Any assistance would be appreciated.

Thanks,
Chris

 

[seth_belgium]

 ' combine Info.
Do While Not rsDetails.EOF
vDetails = vDetails & Trim(rsDetails!Details) & "; "
rsDetails.MoveNext
Loop
vDetails = LTrim(vDetails)

[B]vDetails = Mid(vDetails, 1, Len(vDetails) - 1)[/B]

Seth

 

[eckert1961]

Additional Assistance Please.

Hi Seth,

Thanks for the quick reply. I tried your suggestion but I still get the trailing ;.

Size Chord, Rising Sun - Nunchaku; Size Chain, Rising Sun - Nunchaku;

This is what the combine section looks like now.

' combine Info.
Do While Not rsDetails.EOF
vDetails = vDetails & Trim(rsDetails!Details) & "; "
rsDetails.MoveNext
Loop
vDetails = LTrim(vDetails)
vDetails = Mid(vDetails, 1, Len(vDetails) - 1)

Would you have any additional ideas?

Chris

 

[RV]

' combine Info.
Do While Not rsDetails.EOF
vDetails = vDetails & Trim(rsDetails!Details) & "; "
rsDetails.MoveNext
Loop
vDetails = LTrim(vDetails)
vDetails = Mid(vDetails, 1, Len(vDetails) - [B]2[/B])

RV

 

[eckert1961]

Hi RV,

That gives me the desired result.

Thank you.

Chris

 

[RV]

Good.

A bit of explanation.
You concatenate vDetails by a semicolon plus a space.

Therefore, vDetails will end with a semicolon plus a space.
Hence you need to remove the last two characters.

RV

 

[seth_belgium]

' combine Info.
Do While Not rsDetails.EOF
vDetails = vDetails & Trim(rsDetails!Details) & "; "
rsDetails.MoveNext
Loop
vDetails = LTrim(vDetails)
vDetails = Mid(vDetails, 1, Len(vDetails) - [B]2[/B])

RV

Whoops! Didn't see the space at the end. Sorry!

Seth

 

[eckert1961]

Seth,

Thanks for the explanation.

Cheers,
Chris