Solved

Remove Leading and Trailing Zero's

Posted on 2013-12-31
2
1,992 Views
Last Modified: 2013-12-31
Experts,

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:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28327306.html#a39747020

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",""),"/","")," ","")));
0
Comment
Question by:pdvsa
2 Comments
 
LVL 6

Accepted Solution

by:
ButlerTechnology earned 500 total points
ID: 39748896
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)
Loop

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

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

0
 

Author Closing Comment

by:pdvsa
ID: 39749040
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!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question