Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    8

    Unanswered: Name field partial extract

    I am hoping someone can help me, I know just enough to be dangerous and I need to parse out information from a key field in my database from [first name last name] to [last name]. Is there a simple way to do this? There are no other key fields to link to and there is no comma in the name field.

    Thanks!!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is there a simple way of determining what is the last name?
    having identified what that is then you can move on to developing a mechanism to remove the lastname from the data
    classically the problem is the data, is it....

    lastname, title forename ..... easy to do

    title forename lastname ...realtively easy to do providing you have no double baralled names separate by a space
    IE fforbes-Hamilton... easy
    fforbes Hamilton.. tricky

    does your data include something which come after the lastname (eg a qualification ... trick

    eg
    Professor Ima Nutjob BSc(Hons), BTech, MSc, Phd

    having identified what it is that makes your lastname identifiable then you can develop the mechanism to extract the information

    that could be in a function using say the split() function
    that could be using somehtign like instr to find a specific character
    that could use mid, left or right to extract the required characters
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2007
    Posts
    8
    The name is firstname lastname. No symbols, surnames, titles or suffixes.
    The field lengths are variable. Can you expand on the split() function? Thanks!! Kris

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its as simple as you say then you can either fo this as a function or inline statement

    lastname = right(mycolumn,len(mycolumn)-instr(lastname," "))
    this (should) work by
    using the right function to remove the nth characters from the right mycolumn
    the nth postion is calculated using the instr function to find a specified character " " in the specified string
    it may well have problems with null values
    it may well need tweaking to make it compile (at present its just air code)

    details on the split function can be found in Access help. to use the split function you would have incorporate it into a user function. as you would if you have a more complex requirement.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •