Data Transfer

[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

 

[NG]

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"

 

[Keith Wilby]

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.

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

 

[Wayne-I-M]

UPDATE TableName SET TableName.NewField =
Trim(Right([FieldName],InStr(1,[FieldName] & "nee","")))
WHERE (((TableName.FieldName) Like "*nee*"));

 

[Douglas J. Steele]

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

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)

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

 

[Keith Wilby]

I think you meant Mid, not Right.

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

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.