Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Nov 2006
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using an IIF statement with an InStr (2003)

    I have a query that is using an InStr statement to pull the field "Name" apart at the comma and separating it into two separate fields ("FirstName" and "LastName"). The problem is that some entries in the "Name" field are not separated by a comma and should remain as they are. How can I use an IIF statement to say if there is no comma, then leave the entry alone otherwise split it? Thanks in advance for help anyone can give.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Using an IIF statement with an InStr (2003)

    If there is no comma, then Instr returns a 0.
    So you can test for Instr returning a value greater than 0.

    last: IIf(InStr([name],",")>0,Left([name],InStr([name],",")),[name])

    If Instr >0 (i.e. if there is a comma), then split at the comma, otherwise return the whole name field.
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Nov 2006
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using an IIF statement with an InStr (2003)

    Thank you so much. This works great.

Posting Permissions

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