asked on

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

omgang

use this expression in your query

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

OM Gang

ASKER CERTIFIED SOLUTION

omgang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

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.

shieldsco

ASKER

Thanks

shieldsco

ASKER

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

omgang

Not sure we'll be able to do that in a query expression. Perhaps.....I'll give it a go.
OM Gang

omgang

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

shieldsco

ASKER

Works Good Thanks

omgang

You're welcome.