Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Extract Part of String

    I have some id numbers in a table, which are in the format of S58/1-6. The number before the "/", could be longer or shorter. I want to extract just the text before the "-", so I put this in a field in a query

    Cof: Left$([qryEfacs4.id],InStr(1,[qryEfacs4.id],"-")-1)

    Which I thought was working until I saw an error. It appears that some of the id's don't have the "-", just the S58/1 bit. If this is the case, what do I put in the expression, to display this number, instead of error?

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

    Cof: IIF(InStr(1,[qryEfacs4].[id],"-")>0,Left([qryEfacs4].[id],InStr(1,[qryEfacs4].[id],"-")-1),[qryEfacs4].[id])

    If there is a "-" the Instr returns a positive number, in which case use what you had before, otherwise just return the ID.

    I have changed the bracketting, but if what you had before works you can use that.
    Regards
    John



  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks John. That worked perfectly.

Posting Permissions

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