Solved

Remove Leading and Trailing Zero's

Posted on 2013-12-31
2
2,354 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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

627 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