Locate text based on string criteria 1

[newguy86]

I have a set of data that I need to attach a revision number to. The revision number is located in a log message that is included in the set of data but I'm having difficulty isolating the number so I can put it into the revision number field.

Here's some examples of log messages:

Jane Doe submitted schedule revision number 10.
Marty McFly gave copy traffic confirmation to revision number -1.
Peter Venkman gave managerial approval to revision number 24.

Now the best way that I can think of to do this would be to run a query to pull up the log message for each record and whenever it comes across the string "revision number" in the message, the number that follows that string will be the value inputed into the revision number field.

I figure that once I can identify the string "revision number" then I would just use the Isnumeric() function to find the number that I need whether it's 1 or 100 but I can't seem to find anything that works for me.

So any suggestions you guys might have will be greatly appreciated.

Thanks,

Travis
Charter Media

 

[Andrzejek]

One way...:

Dim str As String

str = "Jane Doe submitted schedule revision number 10."[green]
'str = "Marty McFly gave copy traffic confirmation to revision number -1."
'str = "Peter Venkman gave managerial approval to revision number 24."
[/green]
MsgBox "Rev No is: " & Val(Mid(str, InStr(str, "revision number") + Len("revision number") + 1))

You may want to use ABS() function for the value of -1 (second line of your text)

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.

 

[newguy86]

Here's the formula that I used:

Abs(Mid([logmessage],InStr([logmessage],"revision number")+Len("revision number")+1))

And it works really well except when the log message looks like this:

"Doc Brown exported revision number 0: ."

From what I can tell if there is more than just a period after the revision number the result comes out as #Error.

What can I do to resolve those errors?

Travis
Charter Media

 

[Andrzejek]

There is not much you can do.
Validating text and retrieving valid data out of the text is very hard, After you fix one problem, you will find another 12 that you did not account for. What if you find the text: “Doc Brown exported revision number 15 and is happy now.", or "Doc Brown exported rev no 15 and now is sad."? And 100 other possibilities.

You may ‘scan’ the text character by character and detect the number(s), but that’s not 100% proof method, either.

Sometimes the fastest way – believe it or not – is to do it ‘by-hand’, record by record. But then, from that time on, make your users enter this data into separate field in your DB.


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.

 

[newguy86]

Andrzejek,
I see what you're saying, and I agree with you. Unfortunately this data is part of a report we receive from a 3rd party system and there's no user input in this database, it's just a processor for the data we receive so we can report on it.

And doing it by hand isn't really effective either since there are over 1.5 million records.

Travis
Charter Media

 

[Andrzejek]

In this case I would ‘squeeze’ out of whoever provides you the data a rule that drives of how the revision number is presented. You may get lucky and find out that “the revision number is the last number in the field” in which case you start from the end, evaluate each character, and as soon as you find a number you would keep going backwards and look for the space, which would be (hopefully) the beginning of the number.

Just a guess here...


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.

 

[newguy86]

That's what I've been trying to do but I haven't had any luck yet.

In the meantime, I've modified the code you originally sent me and so far it looks like this:

Abs(Val(Mid([logmessage],InStr([logmessage],"revision number")+Len("revision number")+1)))

I've been spot checking the results and I haven't found any issues as of yet.

Travis
Charter Media

 

[strongm]

Public Function RevNum(strLog As String) As String
Dim myMatch As Object
With CreateObject("vbscript.regexp")
.Pattern = "revision number.+?(\d+)"
Set myMatch = .Execute(strLog)
If myMatch.Count > 0 Then RevNum = myMatch(0).SubMatches(0)
End With
End Function