Fordraiders
asked on
Update Query to Delete everything to thr right: 4th comma
Access 2003
I need an Update query that will delete everything to the left of the 4th comma in a a string.
========================== ========== =
Or maybe I need a function like this Revised:
Function getword(mystring As String) As String
Dim v As Variant
v = Split(Left([mystring], InStr([mystring], ",") - 1), " ")
getword = v(UBound(v))
End Function
UPDATE tblRichText set fldFirstComma = getword(fldRichText)
========================== ========== ======
Sometimes the field may not have 4 commas, or No commas at all.
Thanks
fordraiders
I need an Update query that will delete everything to the left of the 4th comma in a a string.
==========================
Or maybe I need a function like this Revised:
Function getword(mystring As String) As String
Dim v As Variant
v = Split(Left([mystring], InStr([mystring], ",") - 1), " ")
getword = v(UBound(v))
End Function
UPDATE tblRichText set fldFirstComma = getword(fldRichText)
==========================
Sometimes the field may not have 4 commas, or No commas at all.
Thanks
fordraiders
sorry, separator is comma
Function getword(mystring As String) As String
dim vArr
vArr = Split(mystring, ",", 5)
getword=vArr(ubound(vArr))
end function
Function getword(mystring As String) As String
dim vArr
vArr = Split(mystring, ",", 5)
getword=vArr(ubound(vArr))
end function
<Sometimes the field may not have 4 commas, or No commas at all.>
what do you want to do if this is the case?
what do you want to do if this is the case?
Function getWord(s As String) As String
Dim xArr
xArr = Split(s, ",")
If UBound(xArr) < 4 Then
'comma is less then 4
getWord = "we have less then 4 commas"
Else
xArr = Split(s, ",", 5)
getWord = xArr(UBound(xArr))
End If
End Function
update tableName
set [fieldName]=getword([field Name])
set [fieldName]=getword([field
ASKER
Sorry, I meant find the 4th comma and delete everyting to the right !
ASKER
<Sometimes the field may not have 4 commas, or No commas at all.>
what do you want to do if this is the case?
bypass the record
what do you want to do if this is the case?
bypass the record
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks cap !
dim vArr
vArr = Split(mystring, " ", 5)
getword=vArr(ubound(vArr))
end function