How do I delete a value in a field separated by commas

Results 1 to 2 of 2

Thread: How do I delete a value in a field separated by commas

  1. #1
    Join Date
    Dec 1969

    Default How do I delete a value in a field separated by commas

    The values are separated by a comma, ie 27, 127, 130, 227. How do I delete "27" without deleting it from 127, 227, etc. I&#039ve tried the following, however it also deletes it from "127":<BR><BR> OldActivityID = "27"<BR> NewActivityID = "100"<BR><BR> sqlChg_Activity = "Select * From Article" <BR> Set rsChg_Activity = Server.CreateObject ("ADODB.Recordset")<BR> rsChg_Activity.Open sqlChg_Activity, "DSN=camberWebsite",3,3<BR><BR> <BR> While NOT rsChg_Activity.EOF<BR> currActivity = rsChg_Activity ("ProductID")<BR> newService = rsChg_Activity("ServiceID")<BR> If newService = "" Then<BR> newService = NewActivityID<BR> Else<BR> newService = newService & ", " & NewActivityID<BR> End If<BR> <BR> iPos = InStr(currActivity, OldActivityID)<BR> If iPos &#062; 0 then<BR> &#039Replace(searchstr/searchfor/replace with)<BR> currActivity = Replace(currActivity, OldActivityID & ",", "")<BR> Else<BR> iPosEnd = 1<BR> If iPosEnd &#062; 0 then<BR> iPos = InStr(currActivity, OldActivityID) + Len (OldActivityID)<BR> If Not iPos &#062; Len(OldActivityID) Then <BR> End If<BR> iPosEnd = InStr(iPos, currActivity, "")<BR> currActivity = Replace(currActivity, OldActivityID, "")<BR> iLength = iPosEnd - iPos<BR> If Not iPosEnd &#062; 0 Then iLength = Len(s)<BR> iPos = 0<BR> Else<BR> iPos = iPos - (Len(OldActivityID) + 1)<BR> End If<BR> If NewActivityID = "" then<BR> iPosEnd = Len(currActivity)<BR> Else<BR> iPosEnd = Len(currActivity, iPos) & Right(currActivity, iPosEnd)<BR> currActivity = sProduct<BR> If InStr(OldActivity, OldActivityID) = 0 Then<BR> End If<BR> End If<BR> End If<BR> <BR> rsChg_Activity("ProductID") = currActivity<BR> rsChg_Activity("ServiceID") = newService<BR> rsChg_Activity.MoveNext <BR> wEnd<BR><BR> rsChg_Activity.Update<BR> rsChg_Activity.Close<BR>

  2. #2
    John Weeflaar Guest

    Default RE: How do I delete a value in a field separated by commas

    You may need to create your own function instead of using<BR>Replace() function. I think Replace() function will replace<BR>all "27" in the string whether it is from "27" or "127" etc.<BR>Suggestions:<BR>Say the function name: ReplaceNumber(strFull, strNumber)<BR>You need 2 arguments, one for the full string, one for the<BR>string number to delete.<BR>1. Seach for a comma (,) to extract each string number<BR> between 2 commas (except the 1st number having 1 comma).<BR> (Use loop to extract each string number).<BR>2. Trim each and compare it with strNumber.<BR> If they are equal, delete it.<BR>3. You need to extract and reconcatenate in each loop.<BR><BR>I think it should work once you substitute Replace() with<BR>ReplaceNumber().<BR><BR>John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts