Best method Please

[Tom]

Any advice or suggestions on how to achieve the following would be
appreciated:

We have a Access database for our clients in which we have 2 text fields
called Fname & SName, for the Forename & Surname of the client.

We now wish to append a 3rd party Access database to the above database.
Within this database they have a field called contacts. Data held within
this field is in the form:


John Smith
John A Smith
J A Smithe
J A C Jones
There are about 4000 records in this database. What would be the best way of
extracting the forenames from the contact field so that they appended to the
Fname field in the 1st database and also to extract the Surname for
appending to the SName field, again in the 1st database.

Using Access 2003.

TIA

Tom

 

[Allen Browne]

Use an Update query, i.e. in query design view, choose Update on query menu.

Update your first name field to:
Left([contacts], Instr([contacts], " ") - 1)

The surname would be:
Mid([contacts],InstrRev([contacts], " ")+1)

Of course, the last word might not be the surname if there are entries such
as:
John Smith Jnr

 

[Tom]

Hi Allen

Thanks for your advice

The Surname works fine, but using Left([contacts], Instr([contacts], " ") -
1) for the fornames only gives the 1st character/initial not all the
initials or the full forename - any suggestions please

TIA

Tom

Use an Update query, i.e. in query design view, choose Update on query
menu.

Update your first name field to:
Left([contacts], Instr([contacts], " ") - 1)

The surname would be:
Mid([contacts],InstrRev([contacts], " ")+1)

Of course, the last word might not be the surname if there are entries
such as:
John Smith Jnr

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Any advice or suggestions on how to achieve the following would be
appreciated:

We have a Access database for our clients in which we have 2 text fields
called Fname & SName, for the Forename & Surname of the client.

We now wish to append a 3rd party Access database to the above database.
Within this database they have a field called contacts. Data held within
this field is in the form:


John Smith
John A Smith
J A Smithe
J A C Jones
There are about 4000 records in this database. What would be the best way
of extracting the forenames from the contact field so that they appended
to the Fname field in the 1st database and also to extract the Surname
for appending to the SName field, again in the 1st database.

Using Access 2003.

TIA

Tom

 

[Allen Browne]

So you wanted everything before the last space to go into your FName field?

Use InstrRev() to find the last space:
Left([contacts], InstrRev([contacts], " ")-1)

Hopefully you are understanding what these espressions are doing? Now that
you know what to look up in help, it should not be too difficult to figure
out.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

Thanks for your advice

The Surname works fine, but using Left([contacts], Instr([contacts], "
") - 1) for the fornames only gives the 1st character/initial not all the
initials or the full forename - any suggestions please

TIA

Tom

Use an Update query, i.e. in query design view, choose Update on query
menu.

Update your first name field to:
Left([contacts], Instr([contacts], " ") - 1)

The surname would be:
Mid([contacts],InstrRev([contacts], " ")+1)

Of course, the last word might not be the surname if there are entries
such as:
John Smith Jnr

Any advice or suggestions on how to achieve the following would be
appreciated:

We have a Access database for our clients in which we have 2 text fields
called Fname & SName, for the Forename & Surname of the client.

We now wish to append a 3rd party Access database to the above database.
Within this database they have a field called contacts. Data held within
this field is in the form:


John Smith
John A Smith
J A Smithe
J A C Jones
There are about 4000 records in this database. What would be the best
way of extracting the forenames from the contact field so that they
appended to the Fname field in the 1st database and also to extract the
Surname for appending to the SName field, again in the 1st database.

Using Access 2003.

 

[Rob Parker]

PMFJI.

Using Left([contacts], Instr([contacts], " ") - 1) will give all the
characters to the left of the first space in the field of the imported
record. For the examples you gave in your original post:

John Smith will give John
John A Smith will give John
J A Smithe will give J
J A C Jones will give J

I would suggest that in the latter two cases the forename is not "J A" or "J
A C' respectively. All that you know about the forename is that it starts
with "J". If you need it to produce "Jeff" in the first case, or "James" in
the second, then you need software which does not yet exist - except perhaps
in Bill Gates imagination ;-)

If what you are willing to accept as forename is everything to the left of
the last space, then use:
Left([contacts], InstrRev([contacts], " "))

HTH,

Rob

Hi Allen

Thanks for your advice

The Surname works fine, but using Left([contacts], Instr([contacts], "
") - 1) for the fornames only gives the 1st character/initial not all the
initials or the full forename - any suggestions please

TIA

Tom

Use an Update query, i.e. in query design view, choose Update on query
menu.

Update your first name field to:
Left([contacts], Instr([contacts], " ") - 1)

The surname would be:
Mid([contacts],InstrRev([contacts], " ")+1)

Of course, the last word might not be the surname if there are entries
such as:
John Smith Jnr

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Any advice or suggestions on how to achieve the following would be
appreciated:

We have a Access database for our clients in which we have 2 text fields
called Fname & SName, for the Forename & Surname of the client.

We now wish to append a 3rd party Access database to the above database.
Within this database they have a field called contacts. Data held within
this field is in the form:


John Smith
John A Smith
J A Smithe
J A C Jones
There are about 4000 records in this database. What would be the best
way of extracting the forenames from the contact field so that they
appended to the Fname field in the 1st database and also to extract the
Surname for appending to the SName field, again in the 1st database.

Using Access 2003.

TIA

Tom

 

[John Nurick]

Hi Tom,

As others have said, this can get very difficult if you have people with
messy names. If time is more precious than money it's probably worth
taking a look at commercial software such as Splitter for Microsoft
Access (http://www.infoplan.com.au/splitter/). It's not perfect, but
does a better job than most of us could do in a couple of days writing
VBA.

 

[Tom]

Gentlemen - thanks for all your invaluable assistance - the code samples
worked a treat for the majority instance. Apart from the Jnr example Allen
gave if there are surnames like le Bon, or van der Haag caused a similar
problem but as these were only a handful were not a manual issue to manual
correct.

Tom

So you wanted everything before the last space to go into your FName
field?

Use InstrRev() to find the last space:
Left([contacts], InstrRev([contacts], " ")-1)

Hopefully you are understanding what these espressions are doing? Now that
you know what to look up in help, it should not be too difficult to figure
out.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen

Thanks for your advice

The Surname works fine, but using Left([contacts], Instr([contacts], "
") - 1) for the fornames only gives the 1st character/initial not all the
initials or the full forename - any suggestions please

TIA

Tom

Use an Update query, i.e. in query design view, choose Update on query
menu.

Update your first name field to:
Left([contacts], Instr([contacts], " ") - 1)

The surname would be:
Mid([contacts],InstrRev([contacts], " ")+1)

Of course, the last word might not be the surname if there are entries
such as:
John Smith Jnr

Any advice or suggestions on how to achieve the following would be
appreciated:

We have a Access database for our clients in which we have 2 text
fields called Fname & SName, for the Forename & Surname of the client.

We now wish to append a 3rd party Access database to the above
database. Within this database they have a field called contacts. Data
held within this field is in the form:


John Smith
John A Smith
J A Smithe
J A C Jones
There are about 4000 records in this database. What would be the best
way of extracting the forenames from the contact field so that they
appended to the Fname field in the 1st database and also to extract the
Surname for appending to the SName field, again in the 1st database.

Using Access 2003.