Data Transfer

  • Thread starter Thread starter Roger Bell
  • Start date Start date
R

Roger Bell

I have inherited a data base with around 20,000 records. One of the fields
is call "NAME" and contains data like: June Bell nee smith, Joan Thomas nee
jones etc.

What I would like to do is move all the text from nee onwards to a new blank
field.

Is there any way I can do this?

Thanks for any help
 
Hi Roger,

A database field with a name as "Name" or another reserved name isn't a good
idea, but you can rename the field. If you want to split the data in two
fields (I take it you want a field with the first name and one with the
family name) you can use update queries. To split the names you can use the
text function Instr() to look for the spaces , Left(), Right(), or Mid() to
get parts of the text string. Also beware of family names like "Van Halen"
 
Roger Bell said:
I have inherited a data base with around 20,000 records. One of the
fields
is call "NAME" and contains data like: June Bell nee smith, Joan Thomas
nee
jones etc.

What I would like to do is move all the text from nee onwards to a new
blank
field.

Is there any way I can do this?

Thanks for any help
Click to expand...

Hi Roger.

First, change that name field to something other than "NAME" because it's a
reserved word, "fldName" will suffice.

To return the contents of the field from "nee" onwards use the InStr and
Right functions in a query. This works for me:

Right([fldName],InStr([fldName],"nee")+2)

HTH - Keith.
www.keithwilby.co.uk
 
UPDATE TableName SET TableName.NewField =
Trim(Right([FieldName],InStr(1,[FieldName] & "nee","")))
WHERE (((TableName.FieldName) Like "*nee*"));
 
--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Keith Wilby said:
First, change that name field to something other than "NAME" because it's
a reserved word, "fldName" will suffice.
Click to expand...

For a comprehensive list of names to avoid (as well as a link to a free
utility to check your application for compliance), see what Allen Browne has
at http://www.allenbrowne.com/AppIssueBadWord.html
To return the contents of the field from "nee" onwards use the InStr and
Right functions in a query. This works for me:

Right([fldName],InStr([fldName],"nee")+2)
Click to expand...

I think you meant Mid, not Right.

?Right("June Bell nee Smith", InStr("June Bell nee Smith", "nee")+2)
ell nee Smith
?Mid("June Bell nee Smith", InStr("June Bell nee Smith", "nee"))
nee Smith

Unfortunately, that will also split names like Ira Needles (former
chancellor at my old university)

You can avoid that by searching for " nee " instead of "nee" (and adding 1
to the result):

?Mid("June Bell nee Smith", InStr("June Bell nee Smith", " nee ")+1)
nee Smith
 
Douglas J. Steele said:
I think you meant Mid, not Right.

?Right("June Bell nee Smith", InStr("June Bell nee Smith", "nee")+2)
ell nee Smith
Click to expand...

You're correct as always Doug. Right worked for me but I only tested it
quickly on a couple of simple examples, "Smith nee Jones" and "X nee Y".
Apologies to the OP.
 
Back
Top