Feb 01 2022 06:08 PM
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));
Feb 01 2022 10:18 PM - edited Feb 01 2022 10:23 PM
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).
Feb 02 2022 04:17 AM
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.
Feb 02 2022 04:25 AM
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
Feb 02 2022 04:46 AM
Feb 02 2022 05:18 AM
SolutionFeb 02 2022 07:01 AM
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
Feb 02 2022 12:00 PM
Feb 02 2022 12:40 PM
Feb 02 2022 04:09 PM
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.
Feb 03 2022 07:54 AM
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?
Feb 03 2022 06:05 PM
Feb 02 2022 05:18 AM
Solution