Solved

Access String

Posted on 2013-05-23
9
332 Views
Last Modified: 2013-05-23
I would like to remove the first number 1 after the first dash if there are two didgits between the first and second dashes and if the numbers are between 10 and 18. Example

75-11-0350 Before (there are two didgits between the first and second dashs and they are between 10 and 18)
75-1-0350 After

75-10-0350 Before (there are two didgits between the first and second dashs and they are between 10 and 18)
75-0-0350 After

75-19-1216-1031 Before (there are two didgits between the first and second dashs but they are not between 10 and 18)
75-19-1216-1031 After
0
Comment
Question by:shieldsco
  • 4
  • 4
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 39191675
Does the field value always begin with xx-  (where x is any character)
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39191692
revise the function that you have with this one


Function FormatData(strInput As String)
      Dim s() As String
      s = Split(strInput, "-")
      s(1) = IIf(Val(s(1)) >= 10 And Val(s(1)) <= 18, Mid(Val(s(1)), 2), Val(s(1)))
      FormatData = Join(s, "-")
End Function
0
 

Author Comment

by:shieldsco
ID: 39191799
It Coverted the following incorrect:

OriginalTBAppropriation      ConvertAppropriation
75-X-0131                         75-0-0131
75-X-0140                                      75-0-0140
75-X-0512                                      75-0-0512
75-X-0943                                      75-0-0943
75-X-0944                                      75-0-0944
75-X-0954                                      75-0-0954
75-X-0956                                      75-0-0956
75-X-5146-001                       75-0-5146-001
75-X-8250-001                       75-0-8250-001
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39191822
shieldsco,

you never mentioned that you have ALPHA values after the first dash..

we are not mind readers, so please give all the informations..


so what should be the result in case like that?  should it remain the same?
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:shieldsco
ID: 39191834
There should not be a change
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39191841
try this

Function FormatData(strInput As String)
      Dim s() As String
      s = Split(strInput, "-")
     
      If IsNumeric(s) Then
              s(1) = IIf(Val(s(1)) >= 10 And Val(s(1)) <= 18, Mid(Val(s(1)), 2), Val(s(1)))
      End If
     
      FormatData = Join(s, "-")
End Function
0
 

Author Comment

by:shieldsco
ID: 39191895
No conversion on the following -- the change however did convert the X values correctly

OriginalTBAppropriation      ConvertAppropriation
75-12-0955                      75-12-0955  Incorrect s/b 75-2-0955


OriginalTBAppropriation      ConvertAppropriation
75-X-0131                                      75-X-0131 correct
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39191907
oops, missed the (1), use this revised code


Function FormatData(strInput As String)
      Dim s() As String
      s = Split(strInput, "-")
     
      If IsNumeric(s(1)) Then
      s(1) = IIf(Val(s(1)) >= 10 And Val(s(1)) <= 18, Mid(Val(s(1)), 2), Val(s(1)))
      End If
     
      FormatData = Join(s, "-")
End Function
0
 

Author Closing Comment

by:shieldsco
ID: 39191971
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

930 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

11 Experts available now in Live!

Get 1:1 Help Now