Solved

Access 2010 Replace value

Posted on 2013-05-23
9
361 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
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…

685 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