Show only First letter of first name and full last Name

Try this:

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

Select allOpen in new window

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

Example:

Left("Jon Dough",1) & Mid("Jon Dough",  InStrRev("Jon Dough"," ")+1)
Returns
JDough

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

Bill

So ... generic:

Left(YourName,1) & Mid(YourName,  InStrRev(YourName," ")+1)
JDough

SELECT query:

SELECT Left([FullName],1) & Mid([FullName],InStrRev([FullName]," ")+1) AS NewName
FROM Table1
WHERE (((Table1.FullName) Is Not Null));

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

SELECT query:

SELECT Left([FullName],1) & Mid([FullName],InStrRev([FullName],".")+1) AS NewName
FROM Table1
WHERE (((Table1.FullName) Is Not Null));

Works great, thanks!!!!

Sorry for the confusion.  I swear ... I saw a Space instead of a period :-)

mx