Try these --
LName: Mid([tbl1].[userinfo], 17, Instr(20,[tbl1].[userinfo], " ")-16)
FName: Mid([tbl1].[userinfo],InStr(20,[tbl1].[userinfo],"
")+1,InStr(23,[tbl1].[userinfo],", ")-InStr(20,[tbl1].[userinfo]," ")-1)
Then build on the first name one for initals.
--
Build a little, test a little.
"jet04 via AccessMonster.com" 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?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201006/1
.