Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    QRY SEARCH N CHANGE (2003)

    How does one make a qry to look at the first number in a list of numbers, find those that start with 0, then remove the 0 (zero)?

  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: QRY SEARCH N CHANGE (2003)

    If the numbers start with 0 they must be actually in a text field, rather than a number field.

    If that is the case, your query should look like this. You need to make it into an "update query", going up to the Menus Query..Update query.

    My query assumes the field is called field1 - replace that with the real field name.

    with an update query you have to click the red exclamation mark to actually make things happen/.
    Regards
    John



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

    Re: QRY SEARCH N CHANGE (2003)

    A slightly simpler expression for the Update to line would be

    Mid([field1],2)

    If there can be multiple leading zeroes, it becomes more complicated.

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

    Re: QRY SEARCH N CHANGE (2003)

    I always forget to use Mid instead of Right.

    If there could be multiple leading zeros you could just run the query more than once. Each time it will strip away another leading zero.
    Regards
    John



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

    Re: QRY SEARCH N CHANGE (2003)

    Or use the StripLeadingZeros function from <post:=585,136>post 585,136</post:>.

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

    Re: QRY SEARCH N CHANGE (2003)

    What about just changing the field from a text field to a longinteger field. In my limited test that worked perfectly.
    Regards
    John



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

    Re: QRY SEARCH N CHANGE (2003)

    If ALL values are numeric, that is by far the easiest and fastest solution. However, if even one of them is not, for example "00123A", that value will be converted to 0.

  8. #8
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: QRY SEARCH N CHANGE (2003)

    Thanks to you both. I used HansV's instructions and all went well

Posting Permissions

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