Parse Year from Text String

[Guest_imported]

I'm a noob here, trying to parse the year from text in a field, [Editorial].
The first instance of the year in the field always follows this format every time: 19xx. "
That would read: space19xxperiodspacequote

I can get it to hack off everything up to the start of the year using the following expression:

Right(Trim([Editorial]),Len(Trim([Editorial]))-InStr(InStr(1,[Editorial]," 19&quot-1,[Editorial]," &quot)

Result: 1999. "Blah Blah Blah...

How do I also get it to cut off the rest of the field after the first period following the year as well?

Desired Result: 1999

I'm a newbie here and the expression above took me 3 hours and I don't fully understand what I did... Have lost many brain cells this afternnon. :|

Thanks for your help.

SD

 

[cmmrfrds]

Try the Mid function

dim xx as string
xx = Mid(result,starting position,length)
result that is 1999. etc
xx = Mid(result,1,4)

 

[thornmastr]

Mid will work very nicely, but since you have gone to the trouble of getting

result = "2002.sfsdfdf"

Why not just do from that string:

result = left(result,4)
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[JHDavis]

Both are correct; here's another even...

Assuming you are assigning the expression to a query field, and based on your first successful expression, try the following:

Trim(Mid([Editorial],InStr(1,[Editorial]," 19&quot+1,InStr(InStr(1,[Editorial]," &quot+4,[Editorial],"&quot-InStr(1,[Editorial]," &quot))

Result: 19xx

I got the basics from the
Microsoft Knowledge Base Article - Q115915
ACC: Sample Expressions to Extract Portion of Text String

JHDavis