Remove the last comma

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Remove the last comma

Post by adam »

Everything works well with the following code except it puts a comma after the last word when saving to access database. I could I avoid it.

Code: Select all

Private Sub cmdShow_Click()
Dim cnn             As ADODB.Connection
Dim rst             As ADODB.Recordset
Dim strSQL          As String

Dim sList           As String
Dim sList1          As String
Dim nIndex          As Integer

Application.EnableEvents = False
Application.ScreenUpdating = False

For nIndex = 0 To lst_selectedfields.ListCount - 1
    sList = sList & lst_selectedfields.List(nIndex, 0) & ", "
    sList1 = sList1 & lst_selectedfields.List(nIndex, 1) & ", "
Next
sList = Left$(sList, Len(sList) - 1)
sList1 = Left$(sList1, Len(sList1) - 1)

Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
         "Data Source=\\se\file\BackEnd.accdb;"

Set rst = New ADODB.Recordset
rst.Open "tblComma", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

With rst
    .AddNew
    .Fields("Short Serial") = sList
    .Fields("Description") = sList1
    .Update
    
End With

rst.Close
cnn.Close

Set cnn = Nothing

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove the last comma

Post by HansV »

Think carefully. You add ", " - that is a string of length 2.
So what should you do to remove the last ", " ?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Remove the last comma

Post by adam »

I think I got the reason. It's because I had ", " instead of ",". Does this mean with a space it wont work?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove the last comma

Post by HansV »

It will work with ", ", but you will have to change two lines in your code - I gave a hint in my previous reply.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Remove the last comma

Post by adam »

These two?

Code: Select all

  sList = Left$(sList, Len(sList) - 1)
sList1 = Left$(sList1, Len(sList1) - 1) 
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove the last comma

Post by HansV »

Yes, indeed. Try to think what you need to change if you want to remove ", ".
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Remove the last comma

Post by adam »

Like this?

Code: Select all

  sList = Left$(sList, Len(sList) - 2)
sList1 = Left$(sList1, Len(sList1) - 2) 
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove the last comma

Post by HansV »

Why don't you try it? Let me know the outcome.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Remove the last comma

Post by adam »

I've tried and it works.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove the last comma

Post by HansV »

Great!
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Remove the last comma

Post by adam »

Thankyou Hans for the tips and Hints.

Now I'm trying to update an existing record using the same code with minor modifications. Following is how I have compiled it. But I'm getting the error "Data type mismatch in criteria expression".
Column [ID] is autonumber
Column [Short Serial] is short text
Column [Description] is long text

in the excel userform listbox, short serial column has numbers and description column has text and Me.txtDetails.Value has numbers.

any help would be kindly appreciated.

Code: Select all

Private Sub cmdShow_Click()

Dim cnn             As ADODB.Connection
Dim rst             As ADODB.Recordset
Dim strSQL          As String

Dim sList           As String
Dim sList1          As String
Dim nIndex          As Integer

For nIndex = 0 To lst_selectedfields.ListCount - 1
    sList = sList & lst_selectedfields.List(nIndex, 0) & ", "
    sList1 = sList1 & lst_selectedfields.List(nIndex, 1) & ", "
Next
sList = Left$(sList, Len(sList) - 2)
sList1 = Left$(sList1, Len(sList1) - 2)

Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
         "Data Source=\\se\file\BackEnd.accdb;"

Set rst = New ADODB.Recordset

strSQL = "Update tblComma Set [Short Serial]='" & sList & "' , [Description]= '" & sList1 & "' WHERE [ID]= '" & Me.txtDetails.Value & "'"

cnn.Execute strSQL

rst.Open "tblComma", cnn, adOpenKeyset, adLockOptimistic, adCmdTable

rst.Close
cnn.Close

Set cnn = Nothing

End Sub
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove the last comma

Post by HansV »

Since ID is a number field, you should not use single quotes ' around the value of txtDetails.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Remove the last comma

Post by adam »

This is how I've changed.

Code: Select all

strSQL = "Update tblComma Set [Short Serial]='" & sList & "' , [Description]= '" & sList1 & "' WHERE [ID]= " & Me.txtDetails.Value & ""
still I get the same error
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove the last comma

Post by HansV »

Please insert the following lines below the strSQL = ... line:

Code: Select all

Debug.Print strSQL
End
Click the button, then copy the text from the Immediate window into a reply.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Remove the last comma

Post by adam »

Here's the reply

Update tblComma SET [Short Serial]='1, 2' , [Description]= 'All reports done, 2 items sold and sent' WHERE [ID]= 1
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove the last comma

Post by HansV »

That looks OK. Are you absolutely sure that Short Serial is a text field, Description is a text field and ID is a number field?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Remove the last comma

Post by adam »

Sorry. My mistake. The column was not set as a text field. now works fine.
Best Regards,
Adam