Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Remove Leading and Trailing Zero's

Posted on 2013-12-31
2
Medium Priority
?
2,603 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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

824 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