Solved

Remove Leading and Trailing Zero's

Posted on 2013-12-31
2
2,256 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

752 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