Results 1 to 8 of 8

Update Query to Make a String Uniform

  1. #1
    razkowski is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2012
    Posts
    16

    Update Query to Make a String Uniform

    Hello-



    I have a column of data that can very in length and syntax but characters I want are buried in the middle. I'm looking to create an update query to make them uniform. Below are examples of how the data can vary.

    #2690304(8665-176
    #2687654(8638-P192
    #510253(323-133)

    I just need the 3 or 4 digit code after the "(", the hyphen and the 3 or 4 digits after the hyphen. Not including the ")" if it exists.

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,097
    Look at the Mid() function, using InStr() to find the ( and the -.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,625
    Methinks we want to find the left paren, take everything after, and remove the right paren if it's there?
    Thus Mid, Instr and Replace functions?
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    So, like, why do you, like, start every sentence with, like, "so"?

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    19,097
    I misread it, I thought you just wanted the 3-4 digits (8665). Go with Micron's thought.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,120
    Code: mid([Fieldname], instr([Fieldname], "(") + 1, instr([Fieldname], "-") - (instr([Fieldname], "(") + 1))

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,625
    I think I have a shorter version. Will look when I get home.

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,352
    my suggestion

    Code: replace(mid([Fieldname], instr([Fieldname], "(") + 1),")","")

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,625
    Sighhhh, that was it. I imagine we were both testing it on literal values and when you do that, it looks pretty ugly!

    replace(mid("#510253(323-133)", instr("#510253(323-133)", "(") + 1),")","")

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

Similar Threads

  1. Replies: 6
    Last Post: 05-29-2015, 09:21 AM
  2. Update Query - Random Alphanumeric String
    By turkeylord in forum Queries
    Replies: 3
    Last Post: 09-12-2014, 11:55 AM
  3. Help with RunSQL string copied from an update query
    By PlamenGo in forum Programming
    Replies: 14
    Last Post: 04-23-2014, 12:32 PM
  4. Replies: 7
    Last Post: 08-26-2013, 05:05 AM
  5. Replies: 4
    Last Post: 06-19-2012, 11:41 AM

Posting Permissions

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