Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Delete characters in Query

    I have about 90,000 rows in a 2003 Access DB. Field is City. Names are like follows:

    City
    CHERRY VALLEY (CHVL)
    CHERRY VALLEY (CHVL)
    CHERRY VALLEY (CHVL)
    CHERRY VALLEY (CHVL)
    HEMET (HMT)
    HEMET (HMT)
    HEMET (HMT)
    HEMET (HMT)
    LAKE ELSINORE (LKEL)
    LAKE ELSINORE (LKEL)
    LAKE ELSINORE (LKEL)
    LAKE ELSINORE (LKEL)

    I need to run a query that will only delete the parenthesis and any letters in between.

    I can do it with the Find (****) Replace with blank but it takes about ten or 11 passes.

    A query would be really great!

    Any ideas?

    Thanks much Rick.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If the offending text is always at the end, you could use the Left() function along with the InStr() function to find the position of the opening parentheses.
    Paul

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    What Paul said:

    Left(YourField,Instr(YourField,"(")-2)
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    InStr? I though JET used MID...

    EDIT - forget the above: dumb, dumb, dumb.
    Last edited by pootle flump; 05-19-09 at 07:59.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Make an update query

    Before you use action queries, make sure you have a backup of your data.

    Also it's good practice to start them as SELECT queries and review the data before you update it.

    Assuming there is no data where "(" exists on its own and all you want is the text before the first bracket, make a query using this expression to update your strCity (City) field.

    strNewData: IIf(InStr(1,[strCity],"(",0),Left([strcity],InStr(1,[strCity],"(",0)-1),[strCity])
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Garethart - I get a parameter question that states Enter Parameter Value "strCity"

    I'm hoping to have the UpDate Query perform this action on the entire database in the City field.

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Oops . . . when I make the query an Update query it then states that there must be one destination field.

    What am I missing here?

    Thanks

    Rick

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    strCity has to be replaced with the actual name of your field in the underlying table. Apparently it isn't strCity, or else it's not a field in the query, which is why Access is asking for the value.

    The code garethdart is for a calculated field in a Select Query. You would then use the calculated field strNewData in your form/report instead of the original City field. But you can't use it in this fashion, I don't think, for an Update Query. You'd have to use

    IIf(InStr(1,[strCity],"(",0),Left([strcity],InStr(1,[strCity],"(",0)-1),[strCity])

    in the Update To field for the city field in the query.

    The advantage of using a Calculated Field in Select Query, though, is that it will fix the problem in an ongoing fashion, should the need arise, without having to run an Update Query again.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Update Query Does Run but there is no UpDate

    It still displays the (****) after the city name.

    Ideas?

    Thanks.

    Rick

  10. #10
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Ok wait.

    The query worked but it removed the city name plus the (****)

    I gotta be clos with this . . .

    Thanks

    Rick

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    K . . . This time it WORKS!

    Missinglinq had it correctly. I just hadn't removed the str from the code as suggested. Once that was done - FLAWLESS!

    Thanks so much!

    Rick

  12. #12
    Join Date
    May 2009
    Location
    /dev/urandom
    Posts
    6
    Have you tried a
    DELETE FROM (FIELD)
    WHERE (FIELD VALUE) is (xxxxx)?
    UPDATE (FIELD)
    SET (VALUE=xxxxx)
    WHERE (VALUE NUMBER) NOT IN (SELECT xxxxx FROM (FIELD)

    ?
    Can't remener if Acess2k3 has SQLVIEW though...

  13. #13
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad you got it working, Rick!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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