?
Solved

Access String

Posted on 2013-05-23
3
Medium Priority
?
239 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
[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
  • 2
3 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39191320
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 2000 total points
ID: 39191331
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
ID: 39191446
Thanks
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

801 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