Split a Name Field

[Stephenlyn]

Hi Everyone

I have an Access Database that was created by someone else that has mum & dads name in two fields. I want to do a mail merge. The problem is I only want their first names.

eg Field 1 Fred Smith
Field 2 Betty Smith

I want Fred & Betty

any ideas?

cheers

Steve

 

[JGillespie]

Create a calculated field in the query that you are going to base your mail-merge on. The formula for the calculated field should look like this:

FirstNames: Left([Field1],InStr(1,[Field1]," ",0)) & " & " & Left([Field2],InStr(1,[Field2]," ",0))

To create a calculated field simply copy this code into a blank box in the top row (Field Row) of the query design grid.

Good Luck!! Jamie Gillespie
j-gillespie@s-cheshire.ac.uk

 

[MichaelRed]

You need to very careful in parseing name fields. There will generally a large number of variations in the manner the names were entered (syntax and punctuation). Ultimately, you should have seperate fields for each potential part of the name, including 'prefered name' (for 'nicknames' or individuals who 'use' their middle name, ... ). It could be particularly awkward to send corresponsance to John and Jane Doe - only to find out that dear old Jane uses her maiden name (Jane Eyre?)!

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Stephenlyn]

Jamie, You're a ledgend.

I tried to do this using a complicated function from Jimmy The Greek however I kept getting errors using MSA 2000.

Your solution worked like a dream : .

Yes Michael what you say is quite correct, however this is a quick solution to fix a not so well designed database.

Kind regards

Steve