Link to home
Create AccountLog in
Avatar of shieldsco
shieldscoFlag for United States of America

asked on

Access String

I'm using the folling code to replace zero after only the first occurance of a dash:
 Replace([FieldName],"-0","-",1 ,1)
However the codes 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
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Try this:

Function FormatData(strInput as string) as string
      dim s() as string
      s = split(strinput, "-")
      s(1) = Val(s(1))
      formatData = join(s,"-")
End Function

Open in new window


You can call it from a query as follows:

UPDATE YourTable SET YourField = FormatData("" &  YourField )

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of shieldsco

ASKER

Thanks