Separating Text in one field

[summer76]

I am working on an Access 2000 database where I have to manipulate the data into a specific layout. I need an expresion (or something else) that will allow me to separate a suffix from a last name (I also have to do this with first name and middle initials, suites from addresses, etc.). I am able to use an expression to move anything to the right of a space in the suffix column, but if there is not suffix, then the last name moves too. What do I need to make it not move anything to the suffix column when there is no suffix?

The expression I'm using is: Right(Trim([Last Name]),Len(Trim([Last Name]))-InStr(1,[Last Name]," "))

Example of input data:
Last Name
Summer
Summer Jr.

Example of what it needs to be in the output:
Last Name Suffix
Summer
Summer Jr.

 

[PHV]

IIf(InStr(1,Trim([Last Name])," ")>0,Right(Trim([Last Name]),Len(Trim([Last Name]))-InStr(1,[Last Name]," ")),"")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[summer76]

Awesome! Thank you for your help!