Stripping Zeros (2003 allservicepacks)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

You could test like this:

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

Reply | Quote

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:

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

Reply | Quote

Thank you both very much for the time and trouble taken
Steve

Reply | Quote