• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2716
  • Last Modified:

Remove Leading and Trailing Zero's


I have a WHERE condition that strips out characters (" ", "-", "0")
I have encountered an issue with stripping out the "0".

How could the below WHERE condition be modified to to strip out only the LEADING and TRAILING ZERO's?

This is a continuation of a previously answered question here:

thank you

UPDATE [import-CSM2] INNER JOIN tblLetterOfCredit ON [import-CSM2].[Guarantee Code] = tblLetterOfCredit.GuaranteeCode SET tblLetterOfCredit.GuaranteeCode = [import-CSM2].[Guarantee Code]

WHERE (((Replace(Replace(Replace(Replace([LCNO],"-",""),"0",""),"/","")," ",""))=Replace(Replace(Replace(Replace([Reference Number],"-",""),"0",""),"/","")," ","")));
1 Solution
At this point, I think you would be better served with a VBA function make the conversion.  I am not aware of a process that will strip leading characters, but I would be interested if someone has that solution.

Here a VBA function that will strip out the necessary characters:
Public Function ConvertMe(theString As String) As String
' Remove Spaces
  theString = Replace(theString, " ", "")

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

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

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

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

ConvertMe = theString
End Function

Open in new window

You will need to create a Module and copy the code.  
Your where statement should look like:
where Convertme([LCNO]) = ConvertMe([Reference Number])

Open in new window

pdvsaProject financeAuthor Commented:
Tom, that worked perfectly.  just fyi:  I am importing data from our cruddy db and using Access to manipulate the data.  The co's db is absolutely awful.  There are many errors with extra characters etc etc and I have the correct numbers in my separate db (that matches the banks data). Anyways, just thought I would fill you in on this.  

thank you once again!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now