By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,080 Members | 1,855 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,080 IT Pros & Developers. It's quick & easy.

extracting partial field value

P: 4
using access 2000 database. I have a need to extract a partial value in a field. for example I have a table with full names, for example "John Smith", "Jamie Johnson", and I need to be able to extract just the last name. Basically everything right of the last space.

Help?
Jul 17 '07 #1
Share this Question
Share on Google+
3 Replies


missinglinq
Expert 2.5K+
P: 3,532
You have to understand that this will only work if the names are always entered in the format you gave, i.e. "Jamie Johnson."

Expand|Select|Wrap|Line Numbers
  1.  LastName = right(FullName,(len(FullName)-instr(FullName," ")))
Good Luck and Welcome to TheScripts!

Linq ;0)>
Jul 18 '07 #2

P: 4
Thanks, worked great. but like you said, it only works if there is only one space in the field. If the field has a entry like "Jamie V Johnson", "V Johnson" shows. Is there a way to pull just "Johnson"?
Jul 18 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim pos As Integer
  3. Dim newpos As Integer
  4. Dim lastname As String
  5. Dim rpt As Boolean
  6.  
  7.     pos = 1
  8.     rpt = True
  9.  
  10.     Do Until rpt = False
  11.         newpos = InStr(pos + 1, FullName, " ")
  12.         If newpos <> 0 Then
  13.             pos = newpos
  14.         Else
  15.             rpt = False
  16.         End If
  17.     Loop
  18.  
  19.     lastname = Right(FullName, Len(FullName) - pos)
  20.  
Jul 18 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.