Saturday, October 21, 2006

Finding the last instance of a character

I had an issue at work where a VBA application I'd developed was not working due to the persons name. I'll make up the names to retain privacy.
The string I used was in the format "Full Name - StaffID" so for example, "John Smith - 123456".
The program looked for " - " (note the spaces on either side of the dash) and then pulled out everything following that as the staff ID. This worked fine for people like John, and for people like "Sarah-Jane Smith - 789123" and "Derek Parker-Smith - 456789". The problem occured when someone's name had been entered into the system as "Joseph Macfarland - Smith - 321654", because the system took "Smith - 321654" as his staff ID and obviously bugged out.

To rectify this I needed to search the string backwards (in effect).
The easiest way I could find to do this was to split it into an array, using " - " as the delimiter, I could then just use the last entry in the array.
Here's the code I used...

Dim v as Variant
v = split(MainForm.staffmember.text, " - ")
StaffID = v(UBound(v))

a nice simple three lines of code...
Hope this helps you,

Ben

No comments: