Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Feb 01, 2022
Solved

Strip Leading Zero's

Hello Experts,   How could I strip the leading zeros from invoice numbers so that I can simply compare on the non zeros?  My query is below and I have highlighted in red the area that I am looking ...
access
  • arnel_gp's avatar
    arnel_gp
    Feb 02, 2022
    you can also use RegExp to extract the number:

    Public Function fncAlphaNumber(ByVal varInput As Variant) As Variant
    varInput = varInput & ""
    fncAlphaNumber = varInput
    If Len(varInput) < 1 Then
    Exit Function
    End If
    With CreateObject("vbscript.regexp")
    .ignorecase = True
    .Global = True
    .pattern = "[^a-z0-9]"
    varInput = .Replace(varInput, "")
    While Left$(varInput, 1) = "0"
    varInput = Mid$(varInput, 2)
    Wend
    End With
    fncAlphaNumber = varInput
    End Function


    NumInvoice: fncAlphaNumber([Invoice #])
arnel_gp's avatar
arnel_gp
Steel Contributor
Feb 02, 2022
that function will do, the only thing to Get rid is removing the Trailing zeros.

you see 1000 is different with 1 (trailing zeros stripped).
arnel_gp's avatar
arnel_gp
Steel Contributor
Feb 02, 2022
you can also use RegExp to extract the number:

Public Function fncAlphaNumber(ByVal varInput As Variant) As Variant
varInput = varInput & ""
fncAlphaNumber = varInput
If Len(varInput) < 1 Then
Exit Function
End If
With CreateObject("vbscript.regexp")
.ignorecase = True
.Global = True
.pattern = "[^a-z0-9]"
varInput = .Replace(varInput, "")
While Left$(varInput, 1) = "0"
varInput = Mid$(varInput, 2)
Wend
End With
fncAlphaNumber = varInput
End Function


NumInvoice: fncAlphaNumber([Invoice #])
  • Tony2021's avatar
    Tony2021
    Steel Contributor
    Feb 02, 2022

    arnel_gp 

    Hi Arnel,
    I liked your solution since it removed a #error I was getting with the other strippedchar function I was using.

    I do have an issue it seems.

     

    Can I put "Is Null" on the field that is using the the fncAlphaNumber (StrippedInvPC)..pic below?

    The result I am getting is no records, which I know is not correct. 

     

    If I remove the Is Null from the StrippedInvPC field below then I get the result I need but that is only because of the Is Null criteria on the other fields.  I need the Is Null test on the Invoice No (strippedInvPC) as that is where the real test is and the other fields with Is Null criteria are an extra level of surety. 

     

    Please let me know what you think. 

     

     

    • arnel_gp's avatar
      arnel_gp
      Steel Contributor
      Feb 03, 2022
      use:

      Not Is Null

      as your criteria.
      • Tony2021's avatar
        Tony2021
        Steel Contributor
        Feb 03, 2022

        arnel_gp 

         

        wow that worked. Why do I need Not Is Null on that particular field?  it seems to go against the logic.  Is my screen shot below correct?  

         

         

  • Karl_Donaubauer's avatar
    Karl_Donaubauer
    MVP
    Feb 02, 2022

    Review again the requirement, Arnel! 😉

     

    You don't need an external library and regex expressions to strip some leading zeros. An expression with just 3 functions should do the trick:

     

    IIf(IsNumeric([Invoice #]),Val([Invoice #]),[Invoice #])

     

    BTW Tony2021 "Invoice #" is a horrible field name. Because of the special character you always will need to surround it with square brackets. You should change it to sth like "InvoiceNo" ASAP.

     

    Servus
    Karl
    Access News
    Access DevCon

     

     

    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Feb 02, 2022
      thank you Karl. My request could have changed a bit after I grabbed some code (posted above) that was a sort of catch all and not only the leading zeros. I know I should have changed that field name to remove the # sign. its an import excel and would now have to change so many things if I changed it to a proper field name. Ugh. thanks for pointing that out.