Access 2010 Replace value

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
shieldscoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
omgangConnect With a Mentor IT ManagerCommented:
Missed a comma

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

OM Gang
0
 
omgangIT ManagerCommented:
use this expression in your query

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

OM Gang
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
shieldscoAuthor Commented:
Thanks
0
 
shieldscoAuthor Commented:
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
 
omgangIT ManagerCommented:
Not sure we'll be able to do that in a query expression.  Perhaps.....I'll give it a go.
OM Gang
0
 
omgangIT ManagerCommented:
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
 
shieldscoAuthor Commented:
Works Good Thanks
0
 
omgangIT ManagerCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.