[AccessD] Splitting Information from One field into Two

Hi Amanda,

You need an update query with something like

LastName:Left([FullName],Instr(1,[FullName],",")-1)
FirstNameName:Mid([FullName],Instr(1,[FullName],",")+1)

-----------

What these statements do is;

Left = Returns a Variant (String) containing a specified number of 
characters from the left side of a string.

InStr = Returns a Variant (Long) specifying the position of the first 
occurrence of one string within another.

Mid = Returns a Variant (String) containing a specified number of characters 
from a string.

----------------

So the first one grabs everything to the left of the comma and the second 
one grabs everything to the right of the comma.

You are kind of asking for trouble with a field just called NAME in my 
opinion. Could be reserved things using that term.

Gary Kjos
garykjos at hotmail.com





>From: "Amanda Rittenhouse" <ajallen at mail.wvu.edu>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: <accessd at databaseadvisors.com>
>Subject: [AccessD] Splitting Information from One field into Two
>Date: Tue, 28 Sep 2004 12:07:42 -0400
>
>I can't find my sample code where I update two fields by splitting one.  I 
>have a field NAME which contains:
>last name      comma     first name        Example:  Smith, John
>I want to create two fields:  LastName     FirstName
>I want to populate the two fields with the data from NAME.  Before the 
>comma is the last name and after the comma is the first name.  I've seen 
>the update code to do it but can not locate it now.  Please help.
>
>* Amanda
>
>--
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com