Forum Discussion
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 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));
- 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 #])
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).
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.
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)
LoopStrippedChar = 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
