Split Name field into 2 fields

[S Jackson]

When I originally designed my table, I used one field for the Witness name,
called [WitName]. Now, I would like to divide that field into two fields:
[WitFirstName], [WitLastName]. Is there a way to do it without a lot of
manual data entry? If it has to be done with VB code, where do I place the
code? In a separate module?

TIA
S. Jackson

 

[Robert Dickow]

When I originally designed my table, I used one field for the Witness
name, called [WitName]. Now, I would like to divide that field into
two fields: [WitFirstName], [WitLastName]. Is there a way to do it
without a lot of manual data entry? If it has to be done with VB
code, where do I place the code? In a separate module?

I could suggest a coule of tacs to take on this issue. If you have
consistent First + Last names in your current name field, with no middle
initials or reversals of order, you could simply read in the field and
split it when you display it.

Better, however, would be to restructure the table. To do this, you
could add a new last name field, and write a one-time script in whatever
language suits you that would parse the old name fields, split the
names, and then restore them in the new 'first name' and 'last name'
fields that you create, rename, restructure, or whatever. Then, you
could redesign whatever input forms you were using so that you can
conveniently enter the first and last names in the future. Revise any
other parts of your application to suit.

Bob Dickow

 

[S Jackson]

Thank you for your reply. I do not know how to write the code to "parse"
the name field, but your reply gave me the proper search words ("parse name
field") to google it. Came up with this site as a starting point:
http://www.mvps.org/access/strings/str0001.htm

Wish me luck!
S. Jackson

When I originally designed my table, I used one field for the Witness
name, called [WitName]. Now, I would like to divide that field into
two fields: [WitFirstName], [WitLastName]. Is there a way to do it
without a lot of manual data entry? If it has to be done with VB
code, where do I place the code? In a separate module?

I could suggest a coule of tacs to take on this issue. If you have
consistent First + Last names in your current name field, with no middle
initials or reversals of order, you could simply read in the field and
split it when you display it.

Better, however, would be to restructure the table. To do this, you
could add a new last name field, and write a one-time script in whatever
language suits you that would parse the old name fields, split the
names, and then restore them in the new 'first name' and 'last name'
fields that you create, rename, restructure, or whatever. Then, you
could redesign whatever input forms you were using so that you can
conveniently enter the first and last names in the future. Revise any
other parts of your application to suit.

Bob Dickow

 

[Annelie]

Since I do not know what I am doing either, I rely on good old excel to do
the parsing for me, in steps, using text to columns. I seem to have much
better control over data that is not consistent. You can resort your lists
and parse lines that are inconsistent, rows at a time. Than I import the
results, as a new table into access, using the original name field name
along with the new parsed fields.
Add the new fields to your existing table in access. Then do an update query
and then delete the table that come from excel and the old name field from
your old table.
Annelie

Thank you for your reply. I do not know how to write the code to "parse"
the name field, but your reply gave me the proper search words ("parse name
field") to google it. Came up with this site as a starting point:
http://www.mvps.org/access/strings/str0001.htm

Wish me luck!
S. Jackson

When I originally designed my table, I used one field for the Witness
name, called [WitName]. Now, I would like to divide that field into
two fields: [WitFirstName], [WitLastName]. Is there a way to do it
without a lot of manual data entry? If it has to be done with VB
code, where do I place the code? In a separate module?

I could suggest a coule of tacs to take on this issue. If you have
consistent First + Last names in your current name field, with no middle
initials or reversals of order, you could simply read in the field and
split it when you display it.

Better, however, would be to restructure the table. To do this, you
could add a new last name field, and write a one-time script in whatever
language suits you that would parse the old name fields, split the
names, and then restore them in the new 'first name' and 'last name'
fields that you create, rename, restructure, or whatever. Then, you
could redesign whatever input forms you were using so that you can
conveniently enter the first and last names in the future. Revise any
other parts of your application to suit.

Bob Dickow