Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Access String

Posted on 2013-05-23
Medium Priority
366 Views
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
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
• 4
• 4

LVL 77

Expert Comment

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

LVL 120

Expert Comment

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

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 120

Expert Comment

ID: 39191822
shieldsco,

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

so what should be the result in case like that?  should it remain the same?
0

Author Comment

ID: 39191834
There should not be a change
0

LVL 120

Expert Comment

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

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

Rey Obrero (Capricorn1) earned 2000 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

ID: 39191971
Thanks
0

## Featured Post

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with â€¦
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
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â€¦
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increasedâ€¦
###### Suggested Courses
Course of the Month7 days, 2 hours left to enroll