Solved

Access String

Posted on 2013-05-23
3
187 Views
Last Modified: 2013-05-23
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
0
Comment
Question by:shieldsco
  • 2
3 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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

0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
To prevent errors in some special cases, such as no dashes:

Function FormatData(strInput as string) as string

      If Len(strInput) = 0 or Instr(1,strInput,"-") = 0 then
            formatdata = strInput
            exit function
      end if

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

Open in new window

0
 

Author Closing Comment

by:shieldsco
Comment Utility
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Outlook Free & Paid Tools
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now