Solved

Access 2010 Replace value

Posted on 2013-05-23
9
364 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
SendBlaster Pro 4 - Bulk Email Sending Software

SendBlaster 4 Pro - Best Bulk Emailing Sending Software
Automatic Subscribe / Unsubscribe Processing
Great for Newsletters & Mass Mailings
Optional HTML & Text Composition
Integration with Google Features
Built in Spam Score Checking
Free Professional Templates - Feature Packed!

 

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 Office 365 tenants, synchronize your on-premise Active Directory, and implement Single Sign-On.

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…
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 make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
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.

738 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