Forum Discussion
Strip Leading Zero's
- 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 #])
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)
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
- 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).- 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.
- 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 #])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.