Solved

Access 2010 Replace value

Posted on 2013-05-23
9
368 Views
Last Modified: 2013-05-23
I need to remove the 1st zero after the fist dash in an access query.
Example:
75-03-0140 Before
75-3-0140 After

Any thoughts -- Thanks
0
Comment
Question by:shieldsco
[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
  • 5
  • 3
9 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 39190951
use this expression in your query

Replace([FieldName],"-0","-",1) AS Expr1

OM Gang
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 39190955
Missed a comma

Replace([FieldName],"-0","-",,1) AS Expr1

OM Gang
0
 
LVL 21
ID: 39190978
No points needed.

OM Gang, You left out a comma before the 1.

Should be:

Replace([FieldName],"-0","-", ,1)

or

Replace([FieldName],"-0","-",1 ,1)

Otherwise it gets gets leading zeros.
0
Independent Software Vendors: 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!

 

Author Closing Comment

by:shieldsco
ID: 39191195
Thanks
0
 

Author Comment

by:shieldsco
ID: 39191257
I was a little hasty with the code Replace([FieldName],"-0","-",1 ,1)
 does not work in all cases. For Example:

75-01-0140 Before
75-1-0140 After - Ok

75-10-0140 Before
75-10-140 Incorrect -- Only if  zero after the first dash other wise do nothing
75-10-0140 After - Ok
0
 
LVL 28

Expert Comment

by:omgang
ID: 39191280
Not sure we'll be able to do that in a query expression.  Perhaps.....I'll give it a go.
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 39191384
Try this

IIf(InStr([FieldName],"-0")<5,Replace([FieldName],"-0","-",1,1),[FieldName])

Basically, it checks to see if "-0" occurs in the first five characters of the string and only performs the Replace function if so.  It will work if your data is consistent that the "-0" will always be within the first five characters.

OM Gang
0
 

Author Comment

by:shieldsco
ID: 39191439
Works Good Thanks
0
 
LVL 28

Expert Comment

by:omgang
ID: 39191450
You're welcome.
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

691 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