Results 1 to 6 of 6
  1. #1
    wbaroudi is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    10

    Return the value between ()

    Hi guys,

    I wonder if someone can help. I have a description field has lots of data and in the data there is a pair of brackets and inside it a Dx code. This is an outcome of clinical pathway so the code could be (Dx21) or (Dx409).

    I used the
    LeftBracket: InStr([Description],"(") to find the left bracket and
    RightBracket: InStr([Description],")")
    to find the right bracket but I have no idea how to return the in between.

    If the code was (Dx00) then it is easy but sometimes it is (Dx000).

    Any help please would be appreciated.

    Regards
    Wessam

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Just replace the brackets.
    Desc= Replace(Desc,"(","")
    Desc = Replace(Desc,")","")

  3. #3
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Wessam!

    Try this:
    Code:
    Mid([Description], InStr(1, [Description], "("), (InStr(InStr(1, [Description], "("), [Description], ")") - InStr(1, [Description], "(")) + 1)
    Cheers,
    John

  4. #4
    wbaroudi is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    10
    Thank you John,

    That did I appreciated mate.

    I will mark it as resolved.

    Regards
    Wessam

  5. #5
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    You're welcome Wessam!

    I'm happy to help.

    John

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Wessam
    Whilst John's expression works perfectly, for future reference you can combine Replace statements as suggested by Davegri
    Code:
    Replace(Replace(Desc,"(",")"),")","")
    Perhaps less effort than using nested Instr and Mid in an expression?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Return to Top
    By June7 in forum Forum Suggestions
    Replies: 0
    Last Post: 06-07-2019, 02:58 PM
  2. Replies: 6
    Last Post: 04-04-2017, 02:50 AM
  3. Return the last known value
    By Twi78 in forum Queries
    Replies: 1
    Last Post: 10-09-2015, 09:50 AM
  4. return value
    By sharonir22 in forum Queries
    Replies: 1
    Last Post: 05-20-2015, 06:00 AM
  5. Replies: 9
    Last Post: 08-19-2014, 12:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums