Grabbing a string right of a comma.

[jcruzAME]

I've found a lot of stuff for getting what's on the left side of a comma, but I'm trying to grab what's on the right side. I found the following for getting the left side: Left(Me.Employee, InStr(Me.Employee, ",") - 1)

Can I do the same with Right? Using -1 (and other numbers) I still kept getting letters to the left or not all of the letters to the right of the comma.

I also saw something called Split, which I'm looking into.

 

[pbaldy]

Try Mid() with InStr().

 

[jcruzAME]

How would that work? I'm still not completely sure I understand how InStr works, even after looking at the definition of it.

EDIT: The thing is what I'm trying to grab right of the comma I'm never going to know how many characters it is, it's always going to be different.

 

[pbaldy]

In your usage, it finds the first comma in the string. You'd use it the same way you did in the Left() function, but to find the starting point for the Mid() function.

 

[nanscombe]

That would be ...

[URL="http://office.microsoft.com/en-us/access-help/mid-function-HA001228881.aspx"]Mid[/URL](Me.Employee, InStr(Me.Employee, ",") + 1)

Where InStr is used to find the position of the character "," in Me.Employee.

 

[gemma-the-husky]

you also have instrREV (reverse) to find the last comma

use instr (or instrrev) to find the position of the comma.

then use mid (easier than right) to return everything to the right of the comma

usage is mid (string, startposition, length)

if you leave the length blank, you get everything - which is what you want.

 

[jcruzAME]

Thanks for the help guys, got it working. Gemma, thanks for the explanation!