Splitting one field into two.

[Andrew]

Problem: There is a "name" field which contains a data
such as "Bob Donda" or "John Smith."

I need a LastName and FirstName field containing the likes
of "Donda" and "Bob", or "Smith" and "John."

I tried a quick and dirty macro solution to this -- using
SendKeys to send the keystrokes that I would use in order
to perform this split. (I set this up by inserting a new
LastName field directly after the original name field).
This would work, but when the macro attempts to Send a
Shift-Ins key command (to Paste the automatically Cut last
name into the new field), the program complains that I
need the Microsoft Access Utility Add-In, and to install
it... however this is nowhere to be found within setup...
or on the Microsoft web site. Frustratingly enough,
neither the documentation or even the MS web site even
mention such an Add-In.

What am I missing here? Should I be attempting to go
about this in a completely different manner?

Thanks in advance..

 

[Steve Schapel]

Andrew,

Use an Update Query. Update the FirstName field to...
Left([NameField],InStr([NameField]," ")-1)
.... and update the LastName field to...
Mid([NameField],InStr([NameField]," ")+1)

This will work for all records which have the same structure as the
examples you gave, i.e. first-space-last. And it will also work for
any double-barrel surnames, like Fred da Silva or Rip Van Winkel. But
you will need to manually adjust for any records will a space in the
first name component such as Mickey M. Mouse or Bobby Jo McAlister.

- Steve Schapel, Microsoft Access MVP