Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Selecting Part of a field

Posted on 2008-06-23
6
Medium Priority
?
206 Views
Last Modified: 2010-03-20
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
Comment
Question by:mattturley
6 Comments
 
LVL 75
ID: 21848411
Try this:

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

returns smith, john

mx

0
 
LVL 75
ID: 21848421
Generic:

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

mx
0
 
LVL 75
ID: 21848440
Opps .... extra ""

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


Query:

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

mx
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:mattturley
ID: 21848496
Not every record has a middle initial - when this is the case, the above gets rid of the first name.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 21848539

SELECT IIf(InStr([NameField]," ")<>InStrRev([NameField]," "),Left([NameField],InStrRev([NameField]," ")-1),[NameField]) AS Expr1
FROM Table1;
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21850721
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question