Pulling Year Information From Text String

mlcktmguy
mlcktmguy used Ask the Experts™
on
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

Open in new window


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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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
Hamed NasrRetired IT Professional

Commented:
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.
ID Oldest  Newest Number
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
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This function will extract the periods and save them in a table:

Public Function ListPeriods() As Long

    Dim ConcatRecords   As DAO.Recordset
    Dim SingleRecords   As DAO.Recordset
    
    Dim Periods         As Variant
    Dim Years           As Variant
    Dim ID              As Long
    Dim Index           As Integer
    Dim Primo           As Integer
    Dim Ultimo          As Integer
    Dim RecordCount     As Long
    
    Set ConcatRecords = CurrentDb.OpenRecordset("Select * From tblYearsGoverned")
    Set SingleRecords = CurrentDb.OpenRecordset("Select * From tblPeriods")
    
    While Not ConcatRecords.EOF
        ID = ConcatRecords!ID.Value
        Periods = Split(Replace(ConcatRecords!YearsGoverned.Value, "&", ","), ",")
        For Index = LBound(Periods) To UBound(Periods)
            Years = Split(Periods(Index), "-")
            Primo = Years(LBound(Years))
            Ultimo = Years(UBound(Years))
            SingleRecords.AddNew
                SingleRecords!ID.Value = ID
                SingleRecords!FirstYear.Value = Primo
                SingleRecords!LastYear.Value = Ultimo
            SingleRecords.Update
        Next
        ConcatRecords.MoveNext
    Wend
    
    RecordCount = SingleRecords.RecordCount
    SingleRecords.Close
    ConcatRecords.Close
    
    ListPeriods = RecordCount

End Function

Open in new window

And a query will list these:

EE-years.PNG
It's all in the attached demo.
YearInfoForEE.accdb

Author

Commented:
Perfect Gustav, thank you.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial