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"
Show quoteHide quote

Show quote Hide quote 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

Show quote Hide quote 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

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

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.

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



Show quoteHide quote

Need report to only select the "yes" based on month
Conditional formatting based on the Row Source in another control.
Sorting based on selected value...Please help!
Tab Control
Importing dates from fixed-width text files to Access 2007
Linking to selected fields in an external table
Remove Spaces
Time Conversion
relationships
Running procedure on Navigation Button's click