A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

splitting field into 2



 
 
Thread Tools Display Modes
  #1  
Old January 6th, 2006, 08:57 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default splitting field into 2

I am working with Access tables given to me by a client and incorporating
their data into a larger database. The old tables have the first name and
last name in one field, which most of us know it's a no-no in database world.
The last name is first, followed by a comma (no space), then the first name.
Here's an example, "Smith,John".

How can I separate the last name into its own field and likewise for the
first name? The lengths of the names vary, so it's not possible to specify
the number of characters in using Left, Mid, or Right coding. Is there a way
to use the comma as a separator?

Thank you!
  #2  
Old January 6th, 2006, 09:54 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default splitting field into 2

Hi,
check this:
http://support.microsoft.com/default...b;en-us;209045

It shows you sample expressions to do exactly what you need.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps -
http://www.oli-s.de


"Sillie_J" wrote:

I am working with Access tables given to me by a client and incorporating
their data into a larger database. The old tables have the first name and
last name in one field, which most of us know it's a no-no in database world.
The last name is first, followed by a comma (no space), then the first name.
Here's an example, "Smith,John".

How can I separate the last name into its own field and likewise for the
first name? The lengths of the names vary, so it's not possible to specify
the number of characters in using Left, Mid, or Right coding. Is there a way
to use the comma as a separator?

Thank you!

  #3  
Old January 6th, 2006, 10:19 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default splitting field into 2

Perfect!! Thank you!!!! Now I just have to figure out how to put the "Jr."
that occurs after the first name into the last name field after the last name
(e.g., Smith,John Jr.) Whoever created this db table before was not
well-versed... I should be able to figure it out based on the expressions
available on the website you provided. Thanks again.

Jill

"freakazeud" wrote:

Hi,
check this:
http://support.microsoft.com/default...b;en-us;209045

It shows you sample expressions to do exactly what you need.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps -
http://www.oli-s.de


"Sillie_J" wrote:

I am working with Access tables given to me by a client and incorporating
their data into a larger database. The old tables have the first name and
last name in one field, which most of us know it's a no-no in database world.
The last name is first, followed by a comma (no space), then the first name.
Here's an example, "Smith,John".

How can I separate the last name into its own field and likewise for the
first name? The lengths of the names vary, so it's not possible to specify
the number of characters in using Left, Mid, or Right coding. Is there a way
to use the comma as a separator?

Thank you!

  #4  
Old January 6th, 2006, 10:40 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default splitting field into 2

On Fri, 6 Jan 2006 13:19:02 -0800, Sillie_J wrote:

Perfect!! Thank you!!!! Now I just have to figure out how to put the "Jr."
that occurs after the first name into the last name field after the last name
(e.g., Smith,John Jr.) Whoever created this db table before was not
well-versed... I should be able to figure it out based on the expressions
available on the website you provided. Thanks again.

Jill

"freakazeud" wrote:

Hi,
check this:
http://support.microsoft.com/default...b;en-us;209045

It shows you sample expressions to do exactly what you need.
HTH
Good luck
--
Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps -
http://www.oli-s.de

"Sillie_J" wrote:

I am working with Access tables given to me by a client and incorporating
their data into a larger database. The old tables have the first name and
last name in one field, which most of us know it's a no-no in database world.
The last name is first, followed by a comma (no space), then the first name.
Here's an example, "Smith,John".

How can I separate the last name into its own field and likewise for the
first name? The lengths of the names vary, so it's not possible to specify
the number of characters in using Left, Mid, or Right coding. Is there a way
to use the comma as a separator?

Thank you!


If you are using Access 2000 or newer, look up InStrRev() in VBA Help.

Title:
IIf(InStr([CombinedNames],"Jr."),Mid([CombinedNames],InStrRev([CombinedNames],"
")+1),"")

The above will work even if the First Name position includes a space,
i.e.
Sands,Henry Alan Jr.
Sands,H. S. Jr.

Is Jr. the only title you have? What about Sr., Dr., PhD., Ms., etc.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
  #5  
Old January 7th, 2006, 02:19 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default splitting field into 2

Sillie

Save the fields id, Name from old db file als ASCII file with comma as field
separtor. Import the ASCII file into the new db in a new table. relate both
tables on id and use replace name into last name and first name into a new
field First Name

regards

Nanne


"Sillie_J" schreef in bericht
news
I am working with Access tables given to me by a client and incorporating
their data into a larger database. The old tables have the first name and
last name in one field, which most of us know it's a no-no in database
world.
The last name is first, followed by a comma (no space), then the first
name.
Here's an example, "Smith,John".

How can I separate the last name into its own field and likewise for the
first name? The lengths of the names vary, so it's not possible to
specify
the number of characters in using Left, Mid, or Right coding. Is there a
way
to use the comma as a separator?

Thank you!


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Form, Subform, Tab key 2nd_Stage_User Using Forms 17 August 25th, 2006 12:30 AM
Enter text in a field and have it appear as entered elsewhere Gregory Winters General Discussion 6 November 1st, 2005 03:09 AM
Splitting Text Field sfcwarren Running & Setting Up Queries 1 March 7th, 2005 07:22 PM
Splitting a field David# Running & Setting Up Queries 2 February 28th, 2005 04:19 PM
field manipulation Steve Running & Setting Up Queries 2 May 28th, 2004 03:12 PM


All times are GMT +1. The time now is 03:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.