Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dissecting a Field (Access 2003)

    I've been given a database that has a "Name" field but it contains both Last Name, First Name as its contents. Please describe to me indetail how I can separate these two items of information so that I'll have a "First Name" field and a "Last Name" field. Thanks in advance.

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

    Re: Dissecting a Field (Access 2003)

    Create a query based on the table.
    Create a calculated column

    <code>P: InStr([Name],",")</code>

    This returns the position of the comma in the name, or 0 if there is no comma.
    Next, add a second calculated column to the right of the first one:

    <code>FirstName: IIf([P]=0,Null,Trim(Mid([Name],[P]+1)))</code>

    and a third calculated column:

    <code>LastName: IIf([P]=0,[Name],Trim(Left([Name],[P]-1)))</code>

    You can add other fields from the table to this query, and use it as record source for a form and/or a report. If you want to store the first name and last name in the table itself, create FirstName and LastName text fields in the table, then use the expressions described above in an update query.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dissecting a Field (Access 2003)

    <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    You have just helped me out on a similar problem with address fields which were CSV downloads where a ! was inserted
    Jerry

  4. #4
    New Lounger
    Join Date
    Dec 2004
    Location
    West Chester, Pennsylvania, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dissecting a Field (Access 2003)

    I keep getting an error message undefined function iff expression. Please help!

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

    Re: Dissecting a Field (Access 2003)

    You should use <big>IIF</big>, not <big>IFF</big>. IIf stands for <big>I</big>mmediate <big>If</big>.

Posting Permissions

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