Solved

Access String

Posted on 2013-05-23
9
349 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 

Author Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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 …
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

821 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