Strip Leading Zero's

[Tony2021]

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 for a solution. 

I am not sure if this is with Replace? 

thank you. 

SELECT Stampli.Vendor, Stampli.[Invoice #], Import_ExcelPC.[Invoice No], Stampli.[Invoice amount], "these were NOT IN a previous DD" AS ME, Import_ExcelPC.Vendor, Import_ExcelPC.[Invoice amount]
FROM Stampli LEFT JOIN Import_ExcelPC ON (Stampli.[Invoice amount] = Import_ExcelPC.[Invoice amount]) AND (Stampli.Vendor = Import_ExcelPC.Vendor) AND (Stampli.[Invoice #] = Import_ExcelPC.[Invoice No])
WHERE (((Import_ExcelPC.[Invoice No]) Is Null) AND ((Import_ExcelPC.Vendor) Is Null) AND ((Import_ExcelPC.[Invoice amount]) Is Null));

[#2]

Create a Query that will convert to Numeric [Invoice #] Or [Invoice No] (whichever is String):

select *, Val[theFieldname] & "") As NumInvoice from yourTableName;

join this Query to your Other table (using NumInvoice as the joining Column).

[#3]

@arnel_gp 

Hi Arnel, thank you for the reply. I have a follow up if you dont mind.
I created the new query.
The [Invoice #] is a short text field (its a linked table fyi).
When I apply NumInvoice: Val([Stampli].[Invoice #] & "") the result is not as expected on the display.

The original [invoice #] field in the linked table has combinations of letters, dashes, spaces and leading zeros. 

Below is how the data looks. You can see the original invoice # and the modified (NumInvoice). There is an example with a leading 0 (the last record) and the NumInvoice does strip the leading zero but the other invoice# records have been relished to either 0 or all stripped after a non number. 

[#4]

@Tony2021 

Arnel, I ran into this code online:
I tested and it seems to return what I need.

Public Function StrippedChar(theString As String) As String

' Remove Spaces
theString = Replace(theString, " ", "")

' Remove Foward Slash
theString = Replace(theString, "/", "")

' Remove Dash
theString = Replace(theString, "-", "")

' Remove Comma
theString = Replace(theString, ",", "")

' Remove LEADING Zeros
Do Until Left(theString, 1) <> 0
theString = Mid(theString, 2)
Loop

' Remove TRAILING Zeros
Do Until Right(theString, 1) <> 0
theString = Mid(theString, 1, Len(theString) - 1)
Loop

StrippedChar = theString

End Function

here is how it looks with the StrippedChar:

I you happen to have any word for caution please let me know.  

thank you

[#5]

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).

[#6 · Accepted solution]

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 #])

[#7]

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

[#8]

yes that is true. In my query though I have 2 additional criteria (not only on the invoice #)...I add Co Name and Amount as an extra test. I wanted to remove the trailing zeros since I am dealing with 2 excel files and it could be a case where someone left off a trailing zero (there up to 10 sometimes) in 1 file but enter in all of them in the other and while I know stripping trailing zeros will lead to what you said above, adding in company name and amount I think would be a necessary part if going that route. thanks for pointing that out though.

[#9]

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.

[#10]

@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. 

[#11]

use:

Not Is Null

as your criteria.

[#12]

@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?  

[#13]

depends on what are you Filtering.
if you need the filter fields without values, use Is Null.
if you want to filter only those with values, use Not Is Null.