Vignette
asked on
Changing the value of a field in a comma dilimited string
I have a string that looks like this
I want to check that the 18 is the correct value if it is not I want to change it.
I have a query to see if the 18 is there an i replace it with my new value but it replaces all the 18s in the srting
please help
('5208305091008','Noel','S
I want to check that the 18 is the correct value if it is not I want to change it.
I have a query to see if the 18 is there an i replace it with my new value but it replaces all the 18s in the srting
please help
('5208305091008','Noel','S
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is my code but this will replace all the 18s in the string if there are other.I have looked online and dont know how to do it.I am a vb newbey so I need a more help sorry
ioldkey=18
inewkey=19
sFieldValues=('52083050910
If InStr(sFieldValues, iOldKey) Then
sFieldValues = Replace(sFieldValues, iOldKey, iNewKey)
End If
ioldkey=18
inewkey=19
sFieldValues=('52083050910
If InStr(sFieldValues, iOldKey) Then
sFieldValues = Replace(sFieldValues, iOldKey, iNewKey)
End If
Function ReplaceValue(ByVal sourceString as String, ByVal replaceAt as Integer, ByVal oldKey as Integer, ByVal newKey as Integer) as String
Dim arrArray
arrArray = Split(sourceString, ",")
If cInt("0" & arrArray(replaceAt)) = oldKey Then
ReplaceValue = Replace(sourceString, cStr(oldKey), cStr(newKey)
Else
ReplaceValue = sourceString
End If
End Function
I have changed EDDYKT's code to a more generic function which is easy to call. Please don't give me points.
Hope it helps.
Sorry,
following:
ReplaceValue = Replace(sourceString, cStr(oldKey), cStr(newKey)
should be:
ReplaceValue = Replace(sourceString, cStr(oldKey), cStr(newKey))
following:
ReplaceValue = Replace(sourceString, cStr(oldKey), cStr(newKey)
should be:
ReplaceValue = Replace(sourceString, cStr(oldKey), cStr(newKey))
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
What about REPLACE(strData, ",18,", ",19,")
CHeers
CHeers
ASKER
I have used your basic idea I have tested it and it seams to work fine
Well, I was a little late, I modified the code that EDDYKT provided. I think his code would cause the same problem that you had to begin with. It would replace all 18's in the string. Here is code that will do exactly what you want...
Dim str As String
Dim arr
str = "'5208305091008','Noel','S
arr = Split(str, ",")
If (arr(11) = "18") Then
' replace 18 to something else
arr(11) = "19"
End If
str = ""
'Now create the string again
For x = 0 To UBound(arr)
str = str & arr(x) & ","
Next
'Get rid of last comma
str = Left(str, Len(str) - 1)
'Now your str contains a 19 if there was an 18
Dim str As String
Dim arr
str = "'5208305091008','Noel','S
arr = Split(str, ",")
If (arr(11) = "18") Then
' replace 18 to something else
arr(11) = "19"
End If
str = ""
'Now create the string again
For x = 0 To UBound(arr)
str = str & arr(x) & ","
Next
'Get rid of last comma
str = Left(str, Len(str) - 1)
'Now your str contains a 19 if there was an 18
use replace to replace 18 to something else.
You can also use split and join function to parse the string