View Single Post
  #3  
Old June 4th, 2010, 01:29 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Pulling specific data from table field

On Thu, 03 Jun 2010 19:55:28 GMT, "jet04 via AccessMonster.com" u60414@uwe
wrote:

I am having a problem figuring the right way to pull specific data from
within the text of the table's field and separating it into a unique field
alone.

Anyway, my problem is that I need to copy a user's first name out of a field
that contains system user information, and then pull the last name out
separately as well, both uniquely. I don't mind having separate queries to do
this (although one would be preferred of course).

Table: tbl1
Field: userinfo

Example of field data: ABD 000000 111, Doe John, R, W, T22222222, T333333

The ABC and 111 are constants within the field, and the 000000 is a serial
number with a standard length of 6.

I currently have a sql query that pulls the serial number:
UPDATE tbl1
SET tbl1.[Serrial Number] = Right(Left([tbl1].[userinfo],10),6);

Obviously this won't work with for a last name or first name because the
number of letters within a name can change... so I'm stumped as how to
correctly format a query to copy the last name and first name into a separate
field.

Any thoughts?


Karl's code will let you handle the simple cases, but there are names that
don't fit the pattern. What about a name like "Billy Joe McAlister" - first
name Billy Joe (just ask him)? And how about "Hans ten Broek" - first name
Hans, last name ten Broek?

Names are messy, and may well require a USB (Using Someone's Brain) interface.
--

John W. Vinson [MVP]