Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting Part of A Field (Access 2003)

    There is a customer field in my database that is padded with extra characters at the end - 00009 to be exact. Every customer number ends in those numbers. I'm interested in the numbers BEFORE those pad fields, which are the ones that make the customer unique. Unfortunately, the number of digits varies. A customer number could be 100009 or 12345600009.

    How can I remove those trailing five digits?

    The data comes to me with those pad figures, and I need to get rid of them so I have numbers that are consistenly formatted with the data I get from everywhere else (one affiliate has a different data processing system).

    Thanks!

    Nancy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deleting Part of A Field (Access 2003)

    Create a backup copy of the database before trying the following, to be on the safe side.

    Let's say the field is named CustomerNumber.
    Create a query based on the table.
    Add the CustomerNumber field.
    Select Query | Update Query.
    Enter the following expression in the Update to line:

    Left([CustomerNumber], Len([CustomerNumber])-5)

    Select Query | Run and confirm.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2004
    Location
    Minnesota, USA
    Posts
    151
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting Part of A Field (Access 2003)

    Perfect, Hans, thank you.

    I was trying to use Right and Len to subtract the characters - and that, of course, didn't work at all! Hadn't thought of using Left.

    Thank you so much for you fast respose.

    Nancy

Posting Permissions

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