October 22, 2009 at 12:51 pm
I have those first name like below, I will like to extract middle initial
FIRSTNAME
[AARONb][/left] A
AARON ADAN
AARON BRIDGES
A DIX
A GERALD[/i]
A HUSSAM
A JAMES, III
A JEFFREY
I am trying to extract the first name out. like AARON, GERALD, HUSSAM
How can I achive that thrught VBA
Thx
October 27, 2009 at 12:43 pm
Frances L (10/22/2009)
I have those first name like below, I will like to extract middle initialFIRSTNAME
[AARONb][/left] A
AARON ADAN
AARON BRIDGES
A DIX
A GERALD[/i]
A HUSSAM
A JAMES, III
A JEFFREY
I am trying to extract the first name out. like AARON, GERALD, HUSSAM
How can I achive that thrught VBA
Thx
Your post is not clear to me.
Are the names in the data table:
AARON A
AARON ADAN
AARON BRIDGES
A DIX ... ???
Please list the names as they appear in the data table and maybe I can help.
In the meantime lookup Instr() and Mid() functions.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
October 27, 2009 at 12:47 pm
data in the table is
AARON ADAN
AARON BRIDGES
A DIX
A GERALD
A HUSSAM
A JAMES, III
HUSSAM Jr
HUSSAM Jr.
A JAMES Sr.
I want to get the data
as
AARON
AARON
DIX
GERALD
HUSSAM
JAMES
HUSSAM
HUSSAM
JAMES
Thx.
October 27, 2009 at 12:56 pm
Frances L (10/27/2009)
data in the table isAARON ADAN
AARON BRIDGES
A DIX
A GERALD
A HUSSAM
A JAMES, III
HUSSAM Jr
HUSSAM Jr.
A JAMES Sr.
I want to get the data
as
AARON
AARON
DIX
GERALD
HUSSAM
JAMES
HUSSAM
HUSSAM
JAMES
Thx.
How do you tell first name from last name?
You return AARON then AARON then DIX.
I would think you would want:
AARON then AARON then A
or you would want ADAN then BRIDGES then DIX
Sorry but your data is not clear to me.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
October 27, 2009 at 2:39 pm
Frances L
You have posted your question to a site that assists users of SQL Server, with a question concerning how to perform a task in Microsoft's ACCESS.
You would be better served if you posted to a site which helps ACCESS users.
October 27, 2009 at 2:51 pm
bitbucket-25253 (10/27/2009)
Frances LYou have posted your question to a site that assists users of SQL Server, with a question concerning how to perform a task in Microsoft's ACCESS.
You would be better served if you posted to a site which helps ACCESS users.
A very good ACCESS List Server user group is at: http://peach.ease.lsoft.com/scripts/wa.exe?A0=ACCESS-L
Join the group and search the archives and post your questions.
Good luck.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
February 16, 2010 at 8:05 am
You need to find the location of the first space, and truncate the string at that location. To do that, you need to use the instr function.
left([FirstName],len([FirstName]-instr(1,[FirstName]," ")-1)
This will parse off the first name, as long as the first name is one word. If the first name is "Billy Bob", you will get "Billy" only.
In T-Sql it is Substring.
February 19, 2010 at 4:17 pm
Hi Frances L,
As metra-905617 rightly says, you need to find the first instance of a space and then take the characters before it to get the forename or initial, but I'd like to make a few suggestions of my own. Your original post is a few months old, so you may not ever see this, but even so, it may help someone else out there with similar problems to yours and it's actually been a fun little exercise for me to look at and apply my own experiences. 🙂
Firstly, take a step back, and then look at what you're trying to do. Try not to focus on the exact task you're trying to achieve, but instead how it may be part of a potentially larger group of tasks that you may one day have to deal with on a regular basis. What you should ideally realise is that you're trying to extract part of a name, and that there are many other "name-parts" that you can potentially extract. Rather than just do a quick fix to get the initial you're after, consider how you can write a piece of reusable code that you can call again and again (and again and again!) in your application to deal with all the "name-part" issues you're ever likely to face.
As an example, I've written a function below that does just that. To do a quick fix would have taken me a couple of minutes, but writing the function and commenting it for you to understand took me about an hour. Admittedly, it was a considerably more time-consuming solution, but both you and I will probably never ever need to write another procedure to extract "name-parts" again. I can use this code in any MS Access database I work with (or MS Excel spreadsheet or MS Word document), so in the long run I've potentially saved myself unimaginable hours of work. As a developer, you should always be on the lookout for opportunities like this, and the chance to add to what should be your ever-growing library of useful utilities.
Before I come to the code, there are a few things you should consider when dealing with peoples' names, and also that you can never ever rely on having good quality data to deal with. Always assume you'll need to clean the data first so:
1) Remove all leading and trailing spaces - Trim and Trim$ are your friends here. If you're searching for the first or last instance of a space you really don't want to find the that the first or last character is a space. By now, you've hopefully developed the good habit that you can't assume the data you'll have is perfect, and that you'll need to cleanse it, so Trim$() everything first before doing anything else.
2) Remove double, triple and quadruple spaces anywhere in the name you're dealing with.
3) Hyphenated names. These can be a pitfall, but you wouldn't believe how many times you'll hit a Mary-Lou, Jo-Beth or Britney-Christina (the latter especially if you're planning on working in a company that specialises in the nappy/diaper and under-5's market!). In the ideal world they'd be nice and clean, but in the real world you'll remember that you can't guarantee good data. Do a Replace on " - ", " -" and "- " to a "-"; if, as I've mentioned before, you've removed double (or more) spaces this should ensure your hyphenated names are nice and clean, and you can then extract both forenames and surnames quite confidently.
I haven't dealt with titles (e.g. Mrs) as the seemingly infinite misspelt combinations of them mean that you'll need a lookup table to handle them all, but nevertheless, the following code should sort out your problems.
I've over-commented the code so all levels of programmers can understand it, and although I'll rationalise it for my own use, I've tried to keep it simple so the logic flows and it's both readable and understandable.
Firstly, copy all of the code in the pane below into a new module and save the module (call it bas_PersonalNames so it looks professional to other developers). At first glance it may look a hell of a lot of code, but the sea of green are the comments explaining what each line of code does, and in reality, you're only looking at about 50 or 60 lines of code including error-handling and the enumerated constant. Have a read through so you have an idea of what it's doing, but please promise me that if you don't already use error handling, that from now on you always will!!!
After the code I've given a number of examples on how to call the function to give you the parts of a name that you want. Try them, I think you'll be pleasantly surprised at how easy it is to use!
Option Compare Database
Option Explicit
'[Enumerated constants are fantastic for making code easier to use and understand,
' plus, thanks to IntelliSense, they make it look really professional when someone
' calls one of your procedures! By assigning binary values as opposed to
' sequential numbers we can allow users to enter combinations of the values and
' still know exactly which values they've entered.
'
' We use this enumerated constant as the datatype of the second argument in our
' 'BuildName' function below. As an example, if you want forename, middle initial
' and surname then you would enter the argument as:
' ntForename + ntinitialMiddleName + ntSurname
' This equals 21, and because we're using binary, the only possible combination of
' parts of the name they will have chosen is always going to be forename, middle
' initial, and surname. As such, we can the extract them quite easily.
'
' If you still struggle to understand how bitwise comparison works, have a look at
' the on-line help in MS Access to see how VBA uses exactly the same principle to
' show icons and buttons in the MsgBox function. Microsoft use this concept
' everywhere, and if it's good enough for them then it's good enough for us too!]
Public Enum ecNameType
ntForename = 1
ntMiddleName = 2
ntSurname = 4
ntInitialForename = 8
ntinitialMiddleName = 16
ntInitialSurname = 32
End Enum
'====================================================================================
' NAME : BuildName
' PURPOSE : Builds a name based on on a passed full name and the component
' : parts of it that we want to return.
' RETURNS : String - the parts of the name requested, returned as a single
' : string.
' ARGUMENTS : avarFullName - The full name of the person we want to deal with.
' : alngNameParts - The various parts of the name we want to extract
' : and return. This utilises the enumerated constant
' : 'ecNameType' that can be found in the declarations
' : section at the top of this module.
' CREATED BY : Frances L (with grateful thanks to RainbowFfolly and SQL Server
' : Central)
' DATE CREATED : 19/02/2010
'====================================================================================
Public Function BuildName(avarFullName As Variant, _
alngNameParts As ecNameType) As String
'[Create a variable to build the function's return value]
Dim strReturnValue As String
'[A variable to hold any amendments we make to the passed full name]
Dim strNameTemp As String
'[A variable to store the extracted forename]
Dim strForename As String
'[A variable to store the extracted middle name(s) - if any]
Dim strMiddleName As String
'[A variable to store the extracted surnames]
Dim strSurname As String
'[A variable to store the initial of the extracted forename]
Dim strInitialForename As String
'[A variable to store the initial of the extracted middle name(s)]
Dim strInitialMiddleName As String
'[A variable to store the initial of the extracted surname]
Dim strInitialSurname As String
'[Enable error-trapping. This is a MUST and you should always do this in every
' procedure you write!!! From established programmers, you'll earn their respect
' and not their sneers if you always error handle your code]
On Error GoTo Error_Handler
'[Initialise the function's return value to an empty string ("") so it's
' explicitly clear what you're initially going to return]
strReturnValue = vbNullString
'[Make sure we've been passed a name]
If Not IsNull(avarFullName) Then
'[Remove all leading and trailing spaces from the passed name]
strNameTemp = Trim$(avarFullName)
'[Replace all double, triple and quadruple spaces that may be in the name.
' I've used the same line of code three times to do this, but there should
' be a more elegant solution]
strNameTemp = Replace(strNameTemp, " ", " ")
strNameTemp = Replace(strNameTemp, " ", " ")
strNameTemp = Replace(strNameTemp, " ", " ")
'[Fix any spaces around hyphens]
strNameTemp = Replace(strNameTemp, " - ", "-")
strNameTemp = Replace(strNameTemp, "- ", "-")
strNameTemp = Replace(strNameTemp, " -", "-")
'[Make sure the name still contains a space so we can work with it]
If InStr(strNameTemp, " ") > 0 Then
'[Extract the forename by finding the first space and returning all the
' characters before it]
strForename = Left$(strNameTemp, InStr(strNameTemp, " ") - 1)
'[Extract the surname by finding the last space and returning all the
' characters after it]
strSurname = Right$(strNameTemp, _
Len(strNameTemp) - InStrRev(strNameTemp, " "))
'[Remove the forename and surnames from the temporary name string, and
' then trim it of leading and trailing spaces so we're left with any
' middle names - if they exist - and store them in a vaiable]
strNameTemp = Replace(strNameTemp, strForename, vbNullString)
strNameTemp = Replace(strNameTemp, strSurname, vbNullString)
strMiddleName = Trim$(strNameTemp)
'[Extract the initials from the forename and surname]
strInitialForename = Left$(strForename, 1)
strInitialSurname = Left$(strSurname, 1)
'[Extract the initial from the middle name. This is slightly different
' as we need to make sure we actually have a middle name to work with!]
If Len(strMiddleName) > 0 Then
strInitialMiddleName = Left$(strMiddleName, 1)
End If
'[Now that we've extracted all the possible parts of the passed name, we
' get to the clever part - building the name we want to return. We'll use
' bitwise comparison to determine from the value of the passed argument
' 'alngNameParts' exactly what we want to include]
'[Check if we want to return the forename]
If (alngNameParts And ntForename) = ntForename Then
'[Add the forename to the function's return value]
strReturnValue = strReturnValue & strForename
End If
'[Check if we want to return the initial of the forename]
If (alngNameParts And ntInitialForename) = ntInitialForename Then
'[Add the initial of forename to the function's return value]
strReturnValue = strReturnValue & " " & strInitialForename
End If
'[Check if we want to return the middle name(s)]
If (alngNameParts And ntMiddleName) = ntMiddleName Then
'[Add the middle name(s) to the function's return value]
strReturnValue = strReturnValue & " " & strMiddleName
End If
'[Check if we want to return the initial of the first middle name]
If (alngNameParts And ntinitialMiddleName) = ntinitialMiddleName Then
'[Make sure we actually have a middle name]
If Len(strMiddleName) > 0 Then
'[Add the initial of the first middle name to the function's
' return value]
strReturnValue = strReturnValue & " " & strInitialMiddleName
End If
End If
'[Check if we want to return the surname]
If (alngNameParts And ntSurname) = ntSurname Then
'[Add the surname to the function's return value]
strReturnValue = strReturnValue & " " & strSurname
End If
'[Check if we want to return the initial of the surname]
If (alngNameParts And ntInitialSurname) = ntInitialSurname Then
'[Add the initial of surname to the function's return value]
strReturnValue = strReturnValue & " " & strInitialSurname
End If
'[Remove any unwanted spaces at the start that we've picked up building
' the return value]
strReturnValue = LTrim$(strReturnValue)
End If
End If
'[The exit procedure label. With programming, there should always be only way into a
' procedure, and always one way out - this is where the function exits if it either
' executes smoothly or triggers an error]
Exit_Procedure:
'[Have the function return the name we've built and exit the function]
BuildName = strReturnValue
Exit Function
'[This is where we deal with things that go wrong. Thanks to the line of code where we
' said "On Error Goto Error_Handler", all errors will come here]
Error_Handler:
'[Determine what error has just happened]
Select Case Err.Number
'[Any unforeseen error that we don't know about will come here]
Case Else
'[Inform the user that an unforeseen error has occured and display
' important information about it that can help you when debugging]
MsgBox Err.Number & " (BuildName)" & vbCrLf & Err.Description, _
vbCritical, "UNFORESEEN ERROR"
'[Resume execution of the code at the "Exit_Procedure" label just above
' this error handler]
Resume Exit_Procedure
'[This following line of code seems redundant and should never normally
' be executed, but as far as debugging is concerned, it is the greatest
' weapon in your arsenal. When an error happens, and you get the message
' box above, press Ctrl-Break to bring you to this code window.
' Right-click on the line below to bring up the pop-up menu, and select
' 'Set Next Statement' from it. Press F8, and it'll take you straight to
' the line of code that caused the error!]
Resume
End Select
End Function
Using this function is incredibly easy, and to show how it's called we'll use the following sample names in our examples:
1) David John Smith
2) Britney - Christina Georgia Brown
3) Stephen Williams
The various values in the enumerated constant called "ecNameType" at the top of the module, allow us to build a name based on one or multiple combinations of the following "name-parts":
ntForename
ntMiddleName
ntSurname
ntInitialForename
ntinitialMiddleName
ntInitialSurname
Now for the examples on the function's usage...
EXAMPLE 1 - I want the first name and surname from "David John Smith".
BuildName("David John Smith", ntForename + ntSurname)
Returns "David Smith"
EXAMPLE 2 - I want the first name and surname from "Britney - Christina Georgia Brown".
BuildName("Britney - Christina Georgia Brown", ntForename + ntSurname)
Returns "Britney-Christina Brown"
EXAMPLE 3 - I want just the initial from the first name from "Britney - Christina Georgia Brown".
BuildName("Britney - Christina Georgia Brown", ntInitialForename)
Returns "B". Remember that as we're dealing with a hyphenated name, we only want the first character, and we've correctly cleaned it.
EXAMPLE 4 - I want just the initials from the first name and middle name, but also the surname from "Britney - Christina Georgia Brown".
BuildName("Britney - Christina Georgia Brown", ntInitialForename + ntinitialMiddleName + ntSurname)
Returns "B G Brown". As in example 3, we're dealing with a hyphenated name with wrongly entered spaces, but have still returned the correct result
EXAMPLE 5 - I want just the initials from the first name and middle name, but also the surname from "Stephen Williams".
BuildName("Stephen Williams", ntInitialForename + ntinitialMiddleName + ntSurname)
Returns "S Williams". The name we've been looking for doesn't contain a middle name even though we've asked for the initial from it. Even so, we've correctly returned the name we need.
Hopefully from the above examples you've understood how to use the function to extract the various parts from a name. Have a play with it, and try the various combinations so you're comfortable with using it (although I should say that ntInitialForename + ntForename is pretty pointless!).
If it has helped you in any way, please leave a comment - especially if you've learned something. I know it has been a great learning exercise for me, and as one of my first few posts has given me a great opportunity to play around with the formatting features on the board so I can understand how to make my messages clear.
Oh, and I almost forgot... I've now got a function in my code-library that I can use to handle extracting any part of a passed name! 😀
Cheers,
RF
p.s. To any seasoned SQL Server developers out there who've read this far, I'd love any feedback on the basic algorithm I've adopted. I think that due to the majority of the VBA functions I've utilised being SQL-friendly, that I could quite easily convert it for use in as a stored procedure. I'm a relative beginner with SQL Server, so you'll have to give me some leeway, but any advice or criticism is much welcomed. I'd especially appreciate tips on how I could implement something in SQL similar to my use of enumerated constants as an argument in the function.
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
February 22, 2010 at 4:50 am
thx,
February 23, 2010 at 4:10 am
@RF
Your posts are a real treasure trove of information 🙂
February 23, 2010 at 12:07 pm
Cheers grovelli! 🙂
I thought I'd worked out how to use the formatting on the board, but I've just copied the code from the message into a module and it ended up unformatted and lost all the line breaks. Not as helpful as I thought it would be, so I've copied the code into a text file and attached it to this post. At least this way, it can be copied and pasted into a module without any hassle as I intended!
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
March 24, 2010 at 6:55 am
Rainbow Folly...
After seeing your reply in this thread and realizing the effort you put forth I have to say that you have changed my outlook in helping others... and that is what these forums are supposed to be all about. Not only did you go well out of what most would consider the "way", but you also showed that in doing so you received something back in return... a routine to add to your library.
My hat is off to you, never lose what you have because it is a gift to all of us.
Thank you,
John Dickey Jr
March 24, 2010 at 2:52 pm
Hi John,
Blimey, you're making me blush and squirm in embarrassment! 😀
Admittedly, I've plenty of experience in VBA and have used it pretty much on a day-to-day basis for what feels like a life-sentence, but it is nice to be able to share it. As I've never really posted before on forums before, what's really beneficial to me on a personal level is having the opportunity to answer a question like this, and then experiencing the stark realisation that I should try to "explain" my answer clearly if I want to be understood. I'm learning as far as "explaining" is concerned, and although I can't put an absolute value on it as a skill, if I can improve on it, then you can appreciate how important it could prove in both my current job and any interviews I have in the future.
Oh, and thanks for the kind words - they've made my day. 🙂
Cheers,
RF
_____________________________________________________________
MAXIM 106:
"To know things well, we must know the details; and as they are almost infinite, our knowledge is always superficial and imperfect."
Francois De La Rochefoucauld (1613-1680)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply