SQL update to 2 fields; moving data; help please 3

[scar]

Here's what I'm trying to do - and failing!

I have two fields in the contact table - forenames and middlenames.

I'm trying to write a query to update the entire table as follows...

If the forenames field has only one name, do nothing - leave the middlenames field as it is
If the forenames field has more than one name (i.e. at least 2 words with a space between) I want to move the 2nd, 3rd, etc names to the middlenames field. In this scenario I doubt whether there's any data in the middlenames field, so it can be overwritten. The first name should stay on its own in the forenames field.

I can't get my head round this. Is there a way in SQL to do this in one command?

Thanks in advance for your replies.

 

[Eupher]

Try this (please try it on a backup copy of your table, this hasn't been thoroughly tested!):

UPDATE tblContacts SET tblContacts.MiddleName = IIf(InStr(Trim([ForeName])," "), Right([ForeName], Len([ForeName])-InStr([ForeName], " ")), [MiddleName]);

HTH...

Ken S.

 

[Eupher]

Oops, that's only half the battle. Try this instead (same disclaimer applies!):

UPDATE tblContacts SET tblContacts.MiddleName = IIf(InStr(Trim([ForeName]), " "), Right([ForeName], Len([ForeName])-InStr([ForeName], " ")), [MiddleName]),  tblContacts.ForeName = IIf(InStr(Trim([ForeName]), " "), Left([ForeName], InStr([ForeName], " ")-1), Trim([ForeName]));

HTH

Ken S.

 

[Eupher]

One more version for you. The previous query makes no allowances for leading or trailing spaces in the ForeName or MiddleName fields. This query takes account of that possibility:

UPDATE tblContacts SET tblContacts.MiddleName = IIf(InStr(Trim([ForeName]), " "), Right(Trim([ForeName]), Len(Trim([ForeName]))-InStr(Trim([ForeName]), " ")), Trim([MiddleName])), tblContacts.ForeName = IIf(InStr(Trim([ForeName]), " "), Left(Trim([ForeName]), InStr(Trim([ForeName])," ")-1), Trim([ForeName]));

Ken S.

 

[PHV]

Another way:
UPDATE yourTable
SET forenames = Left([forenames], InStr([forenames],' ')-1)
, middlenames = Mid([forenames], InStr([forenames],' ')+1)
WHERE Trim([forenames]) Like '* *'

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

 

[Eupher]

PHV, your solution is much better than mine. A star for you!

Ken S.

 

[scar]

Thanks so much guys. Brilliant!