 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / July 2007if I know the last name can I get the first name from the full name?
I'm inporting data that has the person's full name in one field. i.e. "GLORIA DE LOS SANTOS". But they have given me a last name in a separate field, and it's listed as "LOS SANTOS". Since I know the last name, can I extract everything excluding the last name from the full name? I don't mind if they included the middle initial and that was on the end of the first name. I could live with that. Thanks Kelvin Well, one way to do it is just use the length function and pull from the full name field the field length of the full name field minus length of last name -1 1...got the minus one in there to remove the extra space. > I'm inporting data that has the person's full name in one field. i.e. > "GLORIA DE LOS SANTOS". [quoted text clipped - 9 lines] > > Kelvin You can use this query: SELECT Table1.WholeName, Left([WholeName],Len([WholeName])-Len([LastName])) AS Firstname FROM Table1; > I'm inporting data that has the person's full name in one field. i.e. > "GLORIA DE LOS SANTOS". [quoted text clipped - 9 lines] > > Kelvin Thanks a million for the example! Thanks Kelvin > You can use this query: > SELECT Table1.WholeName, [quoted text clipped - 18 lines] >> >> Kelvin This is what I ended up with.... and it works great! Firstname: StrConv(RTrim(Left([full_name],Len([full_name])-Len([last_name]))),3) This gives me the first name and middle initial if there is one, trims any blank spaces off the right end of the string and puts the text in proper case... Thanks for your help!!! Kelvin > You can use this query: > SELECT Table1.WholeName, [quoted text clipped - 18 lines] >> >> Kelvin So using your example, the person's first name is "Gloria De"? Somehow I don't think so. I would think just "Gloria" is more accurate, as the last name should include the "DE". In any event, another expression to remove the know last name from a fullname, in a query, might be: FirstName:Replace([FullName],[LastNameField],"") You can add the StrConv() and Trim() functions if needed. > This is what I ended up with.... and it works great! > Firstname: [quoted text clipped - 30 lines] > >> > >> Kelvin Thanks I'll give that a try also Kelvin > So using your example, the person's first name is "Gloria De"? > Somehow I don't think so. I would think just "Gloria" is more accurate, as [quoted text clipped - 42 lines] >> >> >> >> Kelvin Hi Kelvin, This is reasonably easy. Rather than importing the data, link to it. I'll assume that fields in the linked table are called FullName and LastName. Then use an append query to move the data into your 'real' table, truncating the full name to get the short name as you go. This is just a matter of getting the number of characters in the last name and omitting that number of characters + 1 from the full name. So the append query needs a calculated field for the FirstName field in your 'real' table, like this: FirstName: Left([Fullname], Len([FullName]) - Len([LastName]) - 1) >I'm inporting data that has the person's full name in one field. i.e. >"GLORIA DE LOS SANTOS". [quoted text clipped - 9 lines] > >Kelvin -- John Nurick - Access MVP Thanks for the reply, this works great! As far as linking to the spreadsheet that seems fine, but the file name is always different. I guess I could allways call it "Import" or something and that could work. I do about the same thing importing the data. Import it, rename it to the table I use for the process. The Excel file is password protected, so I have to deal with that in the process anyway. Thanks for the input and ideas.... Kelvin > Hi Kelvin, > [quoted text clipped - 31 lines] > -- > John Nurick - Access MVP Looking back they haven't been consistant in the tab names either... So unless there is a way to have it prompt the user for the file name and tab name, I think I'll have to do a little tweeking... Thanks Kelvin > Hi Kelvin, > [quoted text clipped - 31 lines] > -- > John Nurick - Access MVP Hi Kelvin, Use replace("GLORIA DE LOS SANTOS","LOS SANTOS","") The function returns "GLORIA DE ". If you don't need the space at the end, just use RTRIM function to remove it. Brent > I'm inporting data that has the person's full name in one field. i.e. > "GLORIA DE LOS SANTOS". [quoted text clipped - 9 lines] > > Kelvin I would be cautious using the Replace function. In 99% plus of the case it would work well, but if the last name should ever be included in the first name you would get strange results.  SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . > Hi Kelvin, > [quoted text clipped - 20 lines] >> >> Kelvin |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.