• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

Selecting Part of a field

I swear I will get this down eventually!

I need to select only part of a User Name field.  Some of the User names have a middle initial at the very end of the string that I need to remove.

I need to convert:

smith, john a to
smith, john

Thanks!
0
mattturley
Asked:
mattturley
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try this:

Mid("smith, john a",1,InStrRev("smith, john a"," ")))

returns smith, john

mx

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Generic:

Mid(FullName,1,InStrRev(FullName"," "))

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Opps .... extra ""

Mid([FullName],1,InStrRev([FullName]," "))


Query:

SELECT Mid([FullName],1,InStrRev([FullName]," ")) AS Expr1
FROM Table1;

mx
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mattturleyAuthor Commented:
Not every record has a middle initial - when this is the case, the above gets rid of the first name.
0
 
Rey Obrero (Capricorn1)Commented:

SELECT IIf(InStr([NameField]," ")<>InStrRev([NameField]," "),Left([NameField],InStrRev([NameField]," ")-1),[NameField]) AS Expr1
FROM Table1;
0
 
GRayLCommented:
As you can get what you want via a query, I recommend you leave your data intact.  Run this query:

SELECT IIF(instr(mytest," ") = instrrev(mytest," "), mytest, Left(mytest,instr(instr(mytest,", ")+2,mytest," "))) AS JustName FROM myTable;
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now