Updating multiple values within a text string programatically where Replace() is not sufficient 1

[MrMode]

I have a dataset with a field that contains a text description which includes year(s).

I need to increase the value of each year found in each text string by 1 year.

I have been looking at Replace() however, while it will find a year, there may be several years in the same field.

If the sentence was "For all entries started in 2008 and completed in 2009"

Then

SELECT Replace([Description],"2008","2009") AS ModifiedDescription
FROM tblShowElementsLink;

would allow me to change the sentence to "For all entries started in 2009 and completed in 2009"

What I need to do is increment both years by 1 year, so it should read

"For all entries started in 2009 and completed in 2010"

Any and all help most appreciated

 

[MajP]

If you know the possible date range you can probably do something like

Public Function IncrementYears(varDescription As Variant) As Variant
  Const startYear = 2000
  Const endYear = 2020
  Dim i As Integer
  If Not IsNull(varDescription) Then
    IncrementYears = varDescription
    For i = endYear To startYear Step -1
      IncrementYears = Replace(IncrementYears, CStr(i), CStr(i + 1))
      'Debug.Print i & ": " & IncrementYears
    Next i
  End If
End Function

SELECT IncrementYears([Description]) AS ModifiedDescription
FROM tblShowElementsLink;

Now there are some hard assumptions. You do not have things like
For all entries started in 2009 and completed in 2010 for property 20201 ...
For all entries started in 2009 and completed in 2010 as a cost of $20,210,000
etc.

Note that you have to look at the dates in reverse order. If not you would increment 2008 to 2009 and then next pass it would increment it to 2010 along with existing 2010.

 

[Andrzejek]

If you have to deal with “text description which includes year(s)”, change your db design and introduce:
[pre]
Number Number
Entries_Start Entries_Compl
2008 2009
...
[/pre]

Easy to add a year or two

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MrMode]

Thanks majP, I will try it and let you know how I get on.

It is an inherited dataset, so sadly, I can't do the dates in a more logical way

Thank you both.