Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 572
  • Last Modified:

Show only First letter of first name and full last Name

Hi Experts,
How do I get the first letter of first name and full last name in query?
example,
Jane.Doe
I want to get is only JDoe.  

I only get to is the first letter is
Name: Left([UserName],1) this gave me the first letter of first name.

Thanks
0
jodstrr2
Asked:
jodstrr2
  • 6
  • 2
  • 2
  • +1
1 Solution
 
sjklein42Commented:
Try this:

LEFT([UserName],1) + SUBSTRING([UserName], CHARINDEX('.', [UserName])+1, 999)

Open in new window

0
 
Bill RossCommented:
Hi,

If the format of the name is Jan.Doe you need to put 2 parts together.

First initial of First Name = Left([Name],1)

Last Name is a bit more tedious.  You need to find the position of the period and get everything after that.
Instr([Name],".") will return the position of the "."
Len([Name]) will return the length of the string.
Last Name then can be computed by Right(Instr([Name],".")+1,Len([Name])-Instr([Name],"."))
Combine the 2 expressions:

Name Like You want =  Left([Name],1) & Right(Instr([Name],".")+1,Len([Name])-Instr([Name],"."))

Regards,

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

Left("Jon Dough",1) & Mid("Jon Dough",  InStrRev("Jon Dough"," ")+1)
Returns
JDough
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Bill RossCommented:
S/B
Name Like You want =  Left([UserName],1) & Right(Instr([UserName],".")+1,Len([UserName])-Instr([UserName],"."))

Bill
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
So ... generic:

Left(YourName,1) & Mid(YourName,  InStrRev(YourName," ")+1)
JDough
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
SELECT query:

SELECT Left([FullName],1) & Mid([FullName],InStrRev([FullName]," ")+1) AS NewName
FROM Table1
WHERE (((Table1.FullName) Is Not Null));
0
 
jodstrr2Author Commented:
I tried
1). sjklein42's soultion, I got the error message "Undefined function "Substring" in expression" and undefined function "CHARINDEX" in expression"

2).BillDenver's, I got the result is first Letter of first name and numbers.  for example, the result is A7, A10 etc.

3). DatabaseMX's, I got the New name as Jane.Doe
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Is there always a period?

If so then:

Left("Jon.Dough",1) & Mid("Jon.Dough",  InStrRev("Jon.Dough",".")+1)
returns
JDough
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
SELECT query:

SELECT Left([FullName],1) & Mid([FullName],InStrRev([FullName],".")+1) AS NewName
FROM Table1
WHERE (((Table1.FullName) Is Not Null));
0
 
jodstrr2Author Commented:
Works great, thanks!!!!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Sorry for the confusion.  I swear ... I saw a Space instead of a period :-)

mx
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now