Pulling numbers from a string

[Meltdown]

I have a column of data set up as follows:

Review July 2003
Review October 2003
Review July 2004
Review October 2004
Review July 2005
Review October 2005...and so on.

I know the data is not normalized, but if I want to use the Year part of the string in a WHERE clause, how would I do that? or in other words how do I extract the Year from the string.

Thanks

 

[RV]

How 'bout the function Right.

RV

 

[Meltdown]

Thanks for the reply RV, sorry I can't use the RIGHT function, some of the data is a bit messy, sometimes it's written as "Review 2004 July" or "2004 Review July"

I know the real solution is to fix the data, but I was hopeing for a quick fix for now.

 

[WayneRyan]

Meltdown,

Just a quick stab:

Public Function fnGetYear(strInput As String) As Long
Dim vntWords As Variant
Dim i As Integer

On Error GoTo ErrorHandler

vntWords = Split(strInput, " ")
For i = 1 To 100
  If IsNumeric(vntWords(i)) Then
     If CLng(vntWords(i)) >= 1901 And CLng(vntWords(i)) <= 2100 Then
        fnGetYear = CLng(vntWords(i))
        Exit Function
     End If
  End If
  Next i

ErrorHandler: <-- Just return 0 on ANY error (zero length string, or whatever)
  fnGetYear = 0
End Function

Put it in a Public Module and call it from a query:

TheYear: fnGetYear([ThatLongStringFieldWithTheEmbeddedNumber)

It will return the year (or 0 if none).

Wayne

 

[Bat17]

Nice
One thing though, Arrays are normaly 0 based so
For i = 0 To 100

HTH

Peter

 

[Meltdown]

Thanks to all,

Wayne, thanks for the code, much appreciated.

Regards
Meltdown