Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2016
    Posts
    2

    Unanswered: Problem forming Query coding

    Hi Guys & Gals
    I am trying to make Access product what I previously created in Excel
    Iam hopeless at Coding so Iam trying to do it in a Query
    I already know its probably hopless, but here goes

    The Excel sheet used two formula to remove a trailing - [dash] from a series
    Kinda clever as not all the series have a trailing dash.. so CANT USE TRIM
    This was the code =LEFT(B2,LEN(B2)-(RIGHT(B2)="-"))
    I would like to duplicate the result in a Query if at all possible

    A series of the numbers looks like this

    07500-032-23-
    07500-032-24-
    07500-032-25-
    07500-032-26-
    07500-032-27-A
    07500-032-27-B
    07500-032-28-A
    07500-032-28-B

    output need is

    07500-032-23
    07500-032-24
    07500-032-25
    07500-032-26
    07500-032-27-A
    07500-032-27-B
    07500-032-28-A
    07500-032-28-B

    Any one got any solutions ?
    They will be most appreciated

    Cheers

    Paul

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 15
    Let's call the table [MyTable] and the column you want to use in the query [Field1]
    The query that will yield the results you're looking for goes like this:
    Code:
    SELECT IIf(Right([Field1],1)= '-', Left([Field1], Len([Field1])-1), [Field1]) AS MyField1
    FROM MyTable;
    If [Field1] can contain NULL values, use:
    Code:
    SELECT IIf(Right([Field1],1)= '-', Left([Field1], Len([Field1])-1), [Field1]) AS MyField1
    FROM MyTable
    WHERE [Field1] IS NOT NULL;
    Have a nice day!

  3. #3
    Join Date
    Dec 2016
    Posts
    2

    Red face Thanks

    Thanks with a bit of testing I managed to work it out using your code
    I ended up drawing on the field called 'sub'
    creating a new field called cleaned
    I don't have to handle null's but nice to have the code..

    Added a line to my Query

    Cleaned: IIf(Right([sub],1)='-',Left([sub],Len([sub])-1),[sub])

    Works a treat

Tags for this Thread

Posting Permissions

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