parse string in access comma delimited field

Question

I want to extract the last portion of a field with a number of ID's of varying lengths separated by a comma. Eg;

ERT43, GHY3, 876

WEDV5, 32, 1KT9, COIU75643

QWSDC8765, 57Y9

I have tried using the right and instr functions without returning the correct result. I need the final ID after the last comma.

thanks

Answer 1

Try this --

Mid([YourField], InStrRev([YourField], " ")+1)

Or if the last segment may have spaces --

Mid([YourField], InStrRev([YourField], ",")+2)

Answer 2

The ParseWord() function from this page may help:

    http://allenbrowne.com/func-10.html

You would use it like this:

    =ParseWord([YourFieldNameHere], -1, ",")

---

Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to forum, rather than allenbrowne at mvps dot org.

Answer 3

Easiest way would be a simple VBA function to return the last item

Public Function fgetLastItem(strIn)

Dim vStr As Variant

If Len(strIn & "") = 0 Then

   fgetLastItem = strIn

Else

   vStr = Split(strIn, ",")

   fgetLastItem = vStr(UBound(vStr))

End If

End Function

Save that in a VBA module and call it where needed

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County