troubleshooting Question

Pulling Year Information From Text String

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
DatabasesMicrosoft Access
5 Comments1 Solution109 ViewsLast Modified:
My client (MC) has gotten a request from one of their clients (TC) for some information stored in the DB.

The TC wants to know what years are covered by an agreement.  Should be a simple request but it complicated by the fact that for each agreement the MC has been entering this information manually into a text field.  Before explaining the requirements I fully realize that because the information is entered manually no logic will be foolproof but I have to make an attempt.

Definition:  Period – Each period should be separated by a comma in the text string.  A period can be a single year (2013) or a span of years (2010-2017).  Each span should have a “-‘ between the beginning year and the ending year.  However the spacing around the dash is inconsistent.  There can be up to 5 periods in a text string
For each period the client wants to know: Oldest Year, Newest Year and Total Number of Years in the Span.

For a period consisting of year 2013, Oldest = 2013, Newest = 2013, Number of Years = 1
For a period 2010 – 2017, Oldest = 2010, Newest = 2017, Number of years = 8

I have created logic that will determine the oldest, newest and number of years in a passed text string.  
Public Sub getMinPeriod_MaxPeriod_TotalInstallPlanPeriods(passedYearsGoverned As String, _
                                                          returnMinAgreementPeriod As Long, _
                                                          returnMaxAgreementPeriod As Long, _
                                                          returnNumberOfPeriods As Long, _
                                                          returnIs2017Included As Boolean)
' this routine will be passed the years governed string from the install plan
' the try to pick out the min year, max year and then calculate the number of periods.
' basically it will create the years one by one starting at 1960.  Then it will see if that year
' exist in the string.  If it does it will make that year eithere the oldest found so far or
' the newest found so far.  At the end of looping thru the years we should have the min and max
Dim wkYearStr As String
Dim wkYearNum As Long
Dim wkMinPeriod As Long
Dim wkMaxperiod As Long
wkMinPeriod = 9999
wkMaxperiod = 0
returnIs2017Included = False
returnIs2017Included = False
For wkYearNum = 1960 To 2017
    wkYearStr = Trim(Str(wkYearNum))
    If InStr(1, passedYearsGoverned, wkYearStr) Then
        ' compare min
        If wkYearNum < wkMinPeriod Then
            wkMinPeriod = wkYearNum
        End If
        '  compare max
        If wkYearNum > wkMaxperiod Then
            wkMaxperiod = wkYearNum
        End If
        ' is 2017 included
        If wkYearNum = 2017 Then
            returnIs2017Included = True
        End If
    End If
Next wkYearNum
' If none found them all must be returned as zero
If wkMinPeriod = 9999 Then
    wkMinPeriod = 0
End If
' calculate the number of periods
returnMinAgreementPeriod = wkMinPeriod
returnMaxAgreementPeriod = wkMaxperiod

returnNumberOfPeriods = wkMaxperiod - wkMinPeriod + 1
End Sub

I now need to develop some logic that will parse thru the entire text string, picking off periods and sending them to my period parsing routine.  That’s where I am having difficulty.  Can anyone help?
I attached a DB including a table of all of the ‘Years Goverend’ text strings in the database.
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros