Trim name. sql query???

[vickriz]

hi there, i have prob in my table fieldname
FirstName
Anthony Jr.
Martin Sr.
Christopher III.

what i want to do is to trim down the name and move the suffixname into my suffix field.
result should be like this:
FirstName Suffix
Anthony Jr.
Martin Sr.
Christopher III.

 

[KenReay]

Hi

You need to understand the use of the functions Right(), Left(), Mid() and Instr() - see Access Help

Before it is possible to give detailed advice, it is necessary to know the 'rules' governing your column FirstName

ie
are there any instances of two names (eg John Paul)?
can one assume that anything comming after a space following the Firstname is a suffix?

assuming No and Yes respectively true

Something like:

FName:=Left([FirstName],Instr(1,[FirstName]," &quot)
Suffix:=Mid([FirstName],Mid(Instr([FirstName]," &quot+1)

Should do it Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK