Right$ Help Please

  • Thread starter Thread starter Philip Martin
  • Start date Start date
P

Philip Martin

I am building a database and need to import some data tables from an old
(Approach) data base.

The imported table has a field in it [Field2] containing the possible
variations:

Steve Brown
Mr. Steve Brown
Mr. S Brown
S Brown

You get the picture?

Now I want to use an UpDate Query to separate off the Surname into a new
field [Surname]. I have tried using the code sample from the MVP site in the
query:

Right$([Field2],Len([Field2])- InStr(1,[Field2],",")-1)

However all that is happening on the UpDate is that the [Surname] field is
being propagated with the contents of the [Field2] but minus the very first
character! i.e. "r. Steve Brown".

Help on this would be much appreciated.

Philip Martin.
 
Philip Martin said:
I am building a database and need to import some data tables from an
old (Approach) data base.

The imported table has a field in it [Field2] containing the possible
variations:

Steve Brown
Mr. Steve Brown
Mr. S Brown
S Brown

You get the picture?

Now I want to use an UpDate Query to separate off the Surname into a
new field [Surname]. I have tried using the code sample from the MVP
site in the query:

Right$([Field2],Len([Field2])- InStr(1,[Field2],",")-1)

However all that is happening on the UpDate is that the [Surname]
field is being propagated with the contents of the [Field2] but minus
the very first character! i.e. "r. Steve Brown".

Help on this would be much appreciated.

Philip Martin.
Click to expand...

That code sample was based on a name in "surname, firstname" format,
with a comma between the two name parts. That won't work in any case
because your name parts are delimited by spaces, regardless of the order
of the parts.

If you're using Access 2000 or later, you could use the InStrRev
function to scan backward for the last space in the [Field2]:

Mid([Field2], InStrRev(Field2]," ")+1)
 
Now I want to use an UpDate Query to separate off the Surname into a new
field [Surname]. I have tried using the code sample from the MVP site in the
query:
Click to expand...

Just an additional concern - Dirk's code will solve this problem, but
you will get errors with people whose last names contain a blank (such
as De La Cruz, Van Hoff, Maynard Smith or the like).
 
John Vinson said:
Now I want to use an UpDate Query to separate off the Surname into a
new field [Surname]. I have tried using the code sample from the MVP
site in the query:
Click to expand...

Just an additional concern - Dirk's code will solve this problem, but
you will get errors with people whose last names contain a blank (such
as De La Cruz, Van Hoff, Maynard Smith or the like).
Click to expand...

Drat! I'd meant to say something about that, but I forgot. Good catch,
John.

When I have to deal with this sort of problem, I write a VBA function to
parse the full-name field and return the requested part of the name.
Then I can incorporate all kinds of squirrelly special cases in the
function. And even then I have to plan to eyeball the results and make
corrections.
 
Dirk Goldgar said:
That code sample was based on a name in "surname, firstname" format,
with a comma between the two name parts. That won't work in any case
because your name parts are delimited by spaces, regardless of the order
of the parts.

If you're using Access 2000 or later, you could use the InStrRev
function to scan backward for the last space in the [Field2]:

Mid([Field2], InStrRev(Field2]," ")+1)
Click to expand...
Dirk

Many thanks for the reply, it worked:o)

I also found that with a bit of modification I extracted the first bits as
well:o))

I accept what JV said about the awkward names, but then filtering that as a
one off (8000 records) will give the receptionist something to do!

BTW,

Many thanks to you and all the other NG responders. Over the last couple of
weeks I have been building a new group bookings data base after not having
used Access for a number of years. All the problems I encountered were
answered by the use of searching through the Access NG's, in no time at all.
Keep up the good work:o)

Philip Martin.
 
Philip Martin said:
Many thanks to you and all the other NG responders. Over the last
couple of weeks I have been building a new group bookings data base
after not having used Access for a number of years. All the problems
I encountered were answered by the use of searching through the
Access NG's, in no time at all.
Click to expand...

That's what we like to hear!

You're welcome.
 
Back
Top