Full Version: First and Last Initial...
UtterAccess Forums > Microsoft® Access > Access Forms
doran_doran
Hi,
I have a field called Name and it has first and last name together.
How can I only take first and last initial (i dont have middle name). see sample below
George Washington
Bill Clinton
Gary Payton
I want my result to be GW, BC, GP
I tried mid command but it's going to work. Please help....
ScottGem
Check this thread
ts really a good idea to search UA before asking a question.
HTH
adaytay
That would be this one then Scott? wink.gif

Ad
doran_doran
Sorry, None of suggestions are working for me.
On the DB I have
Dianna Gomez
**** Clark
Stone Phillips
My result should be
DG
DC
SP
Water is way over my head. Please help.... btw, I did a search b/4 posting the data. Thanks
ScottGem
First you have to parse the name into First and Last. Use the examples in the linked thread to do that. Then you can use the Left() function to get the initials.
doran_doran
My first and last name are in one field called name.
dont have initial or mr/mrs in my names
they look exactly like following
Bill Brown
George Appleside
Norma Goodwin
All I want is to take the first and last initial.. and result want like following
BB
GA
NG
ScottGem
You said that already. And the previous responses have given you the answer.
If you can't get the examples to work, then show us what you have done and we'll see where you are going wrong.
doran_doran
Initial: Left([Primary_Administrator],InStr([Primary_Administrator]," ")+1)
bove gives me First name space last initial
for example, Dianna Ross is now Dianna R
Initial2: Left(InStr([Primary_Administrator]," "),1)
this example gives me , total number of characters "8" for Dianna R
doran_doran
Ok, following is working but I have to create 3 columns.
st column (gives me first name)
Name1: Trim(Left([primary_administrator],InStr([primary_administrator]," ")-1))
2nd column (gives me last name)
Name2: Trim(Mid([primary_administrator],InStr([primary_administrator]," ")+1))
3rd column (gives me initial of each column)
Initial: Left([name1],1) & Left([name2],1)
I guess I will live with this.
yvesdekort
You should be able to put that al together in one column. Just substitute the name1 and name2 with the appropriate Trim(..)
doran_doran
i tried my _ _ _ off and i couldnt do it. feel free to provide me with the code if you can put them together.
yvesdekort
Ok, as you said the method with 3 columns is working, so should this :
nitial: Left(Trim(Left([primary_administrator],InStr([primary_administrator]," ")-1)),1) & Left(Trim(Mid([primary_administrator],InStr([primary_administrator]," ")+1)),1)
SerranoG
strInitials = Left([strName],1) & Mid([strName], Instr([strName], " ") + 1, 1)
ScottGem
Again, if you tried something then you should show us what you tried and let us correct it. We are very willing to help people here, but we don't necessarily want to do the work for you.
If you stop to consider what you have and think it through, you will see its actually pretty simple.
You have 2 sets of letters separated by a space. You want to get the first character and the first character after the space and put them together. So the Left() function takes the first x characters so Left(string,1) gives you the first initial, simple. However getting the last initial is more complex as you need to find the first character after the space. The ideas in the link give you a way of doing that using a combination of the Mid and Instr functions. Which Leaves you with the expressions Greg and Yves gave you.
doran_doran
Both Yves and Greg's code works....
eahhhhhh. thanks to everyone...
Thank you Scott for all the help and let me see from different angles.
quest4
Here is a function, it outputs three initials but can be modified:
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Function fOSUserName() As String ' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = UCase(Left$(strUserName, 3))
Else
fOSUserName = ""
End If
End Function
hth.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.