Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Stripping Zeros (2003 allservicepacks)

    I'm comparing data in two accounting/stock systems. In one, preceeding zeros on invoice numbers are included, whereas in the other they are stripped off. eg Invoice 008239 becomes invoice 8239. The number of zeros can vary. How can I strip off the zeros in the first set of data so I can do linkages between the two (I am checking that the invoice line item exists in both using Invoice No, Line No and Item Purchased as my keys)
    TIA
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: Stripping Zeros (2003 allservicepacks)

    If the invoice numbers are all numbers (in a text field) smaller than about 2,000,000,000, you can use a calculated field CLng([InvoiceNumber]) in a query, and use this for linking. This won't work if the invoice "numbers" have alphabetic parts, or are too large.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping Zeros (2003 allservicepacks)

    Hans
    Thanks again for your quick and excellent reposnse.
    I do have the problem with invoices including letters which show up as #Error in the query. Is there a way I can check if the CLng process gives an error, in which case use the invoice no as it, otherwise use the Clng invoice no
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: Stripping Zeros (2003 allservicepacks)

    You could test like this:

    IIf(IsNumeric([InvoiceNumber]),CLng([InvoiceNumber]),[InvoiceNumber])

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping Zeros (2003 allservicepacks)

    But if the invoice "number" contains alphabetics, and you use it "as is" then the comparison is going to fail if leading zeros are missing. You could use a function like the one below in a calculated field:

    <pre>Public Function StripLeadingZeros(strStr As String) As String
    Dim strWk As String
    strWk = Trim(strStr)
    Do While Len(strWk) > 0 And Left(strWk, 1) = "0"
    strWk = Trim(Right(strWk, Len(strWk) - 1))
    Loop
    StripLeadingZeros = strWk
    End Function
    </pre>

    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stripping Zeros (2003 allservicepacks)

    Thank you both very much for the time and trouble taken
    Steve
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

Posting Permissions

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