Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query help (2003)

    Please could someone help with a query to delete a specific character at the end of a number in a table.

    What I am trying to do is this:

    In my table I have imported a list of invoice numbers but a few of the numbers have a "." at the end of them. What I would like to do is to build a query which will go through all the invoice numbers and delete the "."

    From what I can see the "." is always at the end of the number.

    Here is a sample of what I'm getting at, how can I delete the point from the field in the last line?

    coderef ORDER_NO CUSTOMER STATUS LINE_TYPE WAREHOUSE PRODUCT ORDER_QTY VALUE
    WB|781288M140|2424|504916 504916 20000158 7 P WB 781288M140 2424 5891.53
    WB|781298M140|408|504916 504916 20000158 7 P WB 781298M140 408 991.64
    WB|848625M340|2400|504916 504916 20000158 7 P WB 848625M340 2400 7550.64
    WB|848125M340|1808|504916 504916 20000158 7 P WB 848125M340 1808 12367.08
    WB|848125M340|192|504916. 504916. 20000158 7 P WB 848125M340 192 1313.32

    I have tried an update and a delete query but I cannot get the result I'm looking for.

    Many thanks in advance

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

    Re: Query help (2003)

    You can do this with an update query, but an update query can create havoc if used mistakenly, so first make a copy of the table.

    Make a new query, initially just a select query.

    It is not clear what the name of the ofending field is, so I will cal it InvoiceNo, but you replace this with whatever it is called.

    Select Invocie No where right([InvoiceNo],1)="."

    I.e. put right([InvoiceNo],1)="." on the criteria line in the query grid. Check that the results returned seem OK.

    If they do change the query to an update query and put this on the UpdateTo line:

    Left([InvoiceNo],Len([InvoiceNo])-1)

    This chops the last character of the field, and the criteria remains in place so that it only does it to the right ones.
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query help (2003)

    Excellent John

    Tried it out and it seems to be working fine

    Thanks very much!

Posting Permissions

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