Pulling Year Information From Text String

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.
YearInfoForEE.accdb
Dale Fye

Mickey,

I might consider using a tally table (tbl_Numbers) with one field (lngNumber) and 10 records (the values 0-9).  Then create a query (qry_Years) from that; in your case it might look like:

select cstr(2000 + Tens.lngNumber * 10 + Ones.lngNumber) as strYear
FROM tbl_Numbers as Tens, tbl_Numbers as Ones
WHERE 2000 + Tens.lngNumber * 10 + Ones.lngNumber >= year(date) - 10
AND 2000 + Tens.lngNumber * 10 + Ones.lngNumber <= Year(Date) + 10

This would give you the years 2008-2028, as string values.

Then you could write a query like:

SELECT yourTable.KeyID, Min(Val(qry_Years.strYear)), Max(Val(qry_Years.strYear))
FROM yourTable, qry_Years
WHERE instr(yourTable.Description, qry_Years.strYear) > 0
GROUP By yourTable.KeyID

HTH

Dale
List the required output for the following three records:
ID      YearsGoverned
1      2001-2013
2      1988-2007, 2010-2017
3      2006, 2011, 2012, 2013

Is it like the following? If not amend.
1  2001      2013      13
2 1988       2007       20
2 2012       2013       2
3 2006       2006       1
3 2011       2011       1
3 2012       2012       1
3 2013       2013       1