Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    18

    Question Unanswered: How to remove character in a field using access query

    Hi,
    I have a field in a table values
    100101
    100102
    100103
    100103
    and i wanted to remove the first "1" from the field so that it should look like
    00101
    00102
    00103
    how to write query in access for this, i appreciate your help.
    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    Probably the easiest way would be to use ADO to perform an update query, and when you build your SQL string to perform the update, modify the value with VBA code. If these values are strings, then you can use the Right function to remove all but the first character.
    Make something idiot proof and someone will make a better idiot...

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    SELECT right(YourField, 5)
    FROM yourTable

    **Sidenote: If the field is a numeric datatype, you will lose the leading zeros. If this is the case, and you require the leading zeros, then cast the field to a string before applying the "right" function.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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