Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Stripping - Trim? (Office Xp)

    I am sure this is simple but..... I have a field called name and data is commonly entered as John Smith for example. I need to strip out the John into a field called Firstname and the Smith into a field called Surname. the first name is a variable number of letters from 0 (no first name), 1 (just a letter eg J) through to however long a person's first name is. I am stuck and would appreciate some help. Thanks....Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Stripping - Trim? (Office Xp)

    Assuming that the separator is always one or more spaces, use this in a query:

    FirstName: IIf(InStr([Name]," ")>0,Left([Name],InStr([Name]," ")-1),Null)

    LastName: Trim(Mid([Name],InStr([Name]," ")+1))

    If the Name field can contain "John Smith", but also "Smith, John", the situation becomes mre complicated.

    Note: I avoid a field name such as Name, because it is also a property of many objects, so it can easily lead to confusion.

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Stripping - Trim? (Office Xp)

    awesome - thank you so much.

    Regards.....Peter

Posting Permissions

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