mlcktmguy
asked on
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.
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect Gustav, thank you.
You are welcome!
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)
FROM yourTable, qry_Years
WHERE instr(yourTable.Descriptio
GROUP By yourTable.KeyID
HTH
Dale