Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

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


Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Function getword(mystring As String) As String
dim vArr
vArr = Split(mystring, " ", 5)
getword=vArr(ubound(vArr))

end function
sorry, separator is comma

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?


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([fieldName])
Avatar of Fordraiders

ASKER

Sorry, I meant find the 4th comma and delete everyting to the right !
<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
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks cap !