PC Review


Reply
 
 
=?Utf-8?B?U0pX?=
Guest
Posts: n/a
 
      8th Nov 2006
I HAVE A LARGE DATA BASE THAT WAS CREATED IN A FORMAT OF THE WHOLE NAME IS IN
ONE CELL I NEED TO SPLIT "JONES, BILL" WHICH ARE IN ONE CELL INTO TWO CELLS

THANKS IN ADVANCE FOR YOUR HELP

SJW
 
Reply With Quote
 
 
 
 
Rick B
Guest
Posts: n/a
 
      8th Nov 2006
Please stop shouting at us (turn off your caps lock).

If you will search, you'll find numerous previous posts that address this
topic. Here is a portion of one of those posts...

You can add a new column to your query for the first name, and a column for
the last name. You can then use the values from the query in your forms,
reports, and other queries. I'd probably make this into an update query and
clean up my table and maintain the two fields from this point forward and
delete the combined field.

FName: Left([Name],InStr([Name],",")-1)

LName: Mid([Name],InStr([Name],",")+1)


--
Rick B




"SJW" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I HAVE A LARGE DATA BASE THAT WAS CREATED IN A FORMAT OF THE WHOLE NAME IS
>IN
> ONE CELL I NEED TO SPLIT "JONES, BILL" WHICH ARE IN ONE CELL INTO TWO
> CELLS
>
> THANKS IN ADVANCE FOR YOUR HELP
>
> SJW



 
Reply With Quote
 
 
 
 
=?Utf-8?B?S2VuIFNoZXJpZGFu?=
Guest
Posts: n/a
 
      8th Nov 2006
Add FirstName and LastName fields to the table and then run an update query
to populate them with values parsed from the existing field, assumed to be
called FullName in this example, e.g.

UPDATE YourTable
SET FirstName = MID(Fullname,INSTR(FullName," ")+1),
LastName = LEFT(FullName, INSTR(FullName,",")-1);

This assumes all the values in the FullName field are in exactly the same
format as in your example , i.e. the last name followed by a comma, then a
space, then the first name.

Remember that if table or column names have spaces or other special
characters in them you need to wrap them in square brackets [like this].

BTW spreadsheets have cells, database tables have rows and columns (aka
rather inaccurately as records and fields). A database is a very different
animal from a spreadsheet.

Ken Sheridan
Stafford, England

"SJW" wrote:

> I HAVE A LARGE DATA BASE THAT WAS CREATED IN A FORMAT OF THE WHOLE NAME IS IN
> ONE CELL I NEED TO SPLIT "JONES, BILL" WHICH ARE IN ONE CELL INTO TWO CELLS
>
> THANKS IN ADVANCE FOR YOUR HELP
>
> SJW


 
Reply With Quote
 
=?Utf-8?B?U0pX?=
Guest
Posts: n/a
 
      8th Nov 2006
RICK, THANKS FOR YOUR HELP. ALL CAPS FOR GUYS THAST ARE 70 AND DON;T SEE SO
WELL SORRY TO OFFEND YOU

SJW

"Rick B" wrote:

> Please stop shouting at us (turn off your caps lock).
>
> If you will search, you'll find numerous previous posts that address this
> topic. Here is a portion of one of those posts...
>
> You can add a new column to your query for the first name, and a column for
> the last name. You can then use the values from the query in your forms,
> reports, and other queries. I'd probably make this into an update query and
> clean up my table and maintain the two fields from this point forward and
> delete the combined field.
>
> FName: Left([Name],InStr([Name],",")-1)
>
> LName: Mid([Name],InStr([Name],",")+1)
>
>
> --
> Rick B
>
>
>
>
> "SJW" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >I HAVE A LARGE DATA BASE THAT WAS CREATED IN A FORMAT OF THE WHOLE NAME IS
> >IN
> > ONE CELL I NEED TO SPLIT "JONES, BILL" WHICH ARE IN ONE CELL INTO TWO
> > CELLS
> >
> > THANKS IN ADVANCE FOR YOUR HELP
> >
> > SJW

>
>
>

 
Reply With Quote
 
Rick B
Guest
Posts: n/a
 
      8th Nov 2006
NO PROBLEM

:-)

--
Rick B



"SJW" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> RICK, THANKS FOR YOUR HELP. ALL CAPS FOR GUYS THAST ARE 70 AND DON;T SEE
> SO
> WELL SORRY TO OFFEND YOU
>
> SJW
>
> "Rick B" wrote:
>
>> Please stop shouting at us (turn off your caps lock).
>>
>> If you will search, you'll find numerous previous posts that address this
>> topic. Here is a portion of one of those posts...
>>
>> You can add a new column to your query for the first name, and a column
>> for
>> the last name. You can then use the values from the query in your forms,
>> reports, and other queries. I'd probably make this into an update query
>> and
>> clean up my table and maintain the two fields from this point forward and
>> delete the combined field.
>>
>> FName: Left([Name],InStr([Name],",")-1)
>>
>> LName: Mid([Name],InStr([Name],",")+1)
>>
>>
>> --
>> Rick B
>>
>>
>>
>>
>> "SJW" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >I HAVE A LARGE DATA BASE THAT WAS CREATED IN A FORMAT OF THE WHOLE NAME
>> >IS
>> >IN
>> > ONE CELL I NEED TO SPLIT "JONES, BILL" WHICH ARE IN ONE CELL INTO TWO
>> > CELLS
>> >
>> > THANKS IN ADVANCE FOR YOUR HELP
>> >
>> > SJW

>>
>>
>>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
String.Split versus Strings.Split kurt sune Microsoft VB .NET 5 30th Mar 2005 04:53 PM
Split view - vertically split?? Louie Perkins Microsoft Frontpage 3 29th Mar 2004 11:14 PM
Re: TO SPLIT OR NOT TO SPLIT Arvin Meyer Microsoft Access 0 15th Sep 2003 12:22 PM
Re: TO SPLIT OR NOT TO SPLIT Tony Toews Microsoft Access 0 15th Sep 2003 12:52 AM
Re: TO SPLIT OR NOT TO SPLIT Kevin3NF Microsoft Access 1 14th Sep 2003 11:24 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:23 AM.