Extracting Text From A Field

[jdraw]

Show us your table design.
What are:
EngineFullDetail
type????

 

[EzGoingKev]

Show us your table design.
What are:
EngineFullDetail
type????

EngineFullDetail is the table's field name I want to extract the data from.

Type is the first delimiter.

 

[MajP]

And I am not getting any result back. Not sure why

That is a completely different problem from the first. In the first example you create an array of every word. Search for "Vin" and then return the next word.
In this problem if you split on "-" you get 3 strings

L6
9.3L 570ci DIESEL DI Turbo/Aftercooled/Intercooled type MaxxForce 10
4 valve SOHC

None of those strings = "Type" so no value is returned.
You need some different code to do that.

 

[MajP]

Something like this would work

Public Function GetType(strIn As String) As String
  GetType = Split(strIn, "Type")(1)
  GetType = Split(GetType, "-")(0)
End Function

 

[EzGoingKev]

I guess my understanding of how the function works is incorrect.

Using my example:

L6 - 9.3L 570ci DIESEL DI Turbo/Aftercooled/Intercooled type MaxxForce 10 - 4 valve SOHC

I thought it would pull all text after the word "type" and stop at the second delimiter which I set as "-".

 

[MajP]

No. But this would be a generic function to pass in where to start and end.

Public Function GetAllAfter(strIn As String, AfterDelimiter As String, Optional StopDelimiter As String) As String
 
  GetAllAfter = Split(strIn, AfterDelimiter)(1)
  GetAllAfter = Split(GetAllAfter, StopDelimiter)(0)
End Function
Public Sub test()
  Debug.Print GetAllAfter("L6 - 9.3L 570ci DIESEL DI Turbo/Aftercooled/Intercooled type MaxxForce 10 - 4 valve SOHC", "Type", "-")
End Sub

 

[jdraw]

I agree with MajP - this is a different issue.Originally we were separating fragments based on a single delimiter. In your latest request, the fragments/string are not all separated by "-". You will have to decide if you want the leading and trailing space OR Trim the result.

 

[arnelgp]

Public Function betweenWords(ByVal theString As String, ByVal start_word As String, ByVal end_word As String) As String
    Dim i As Integer
    i = InStrRev(theString, start_word)
    If i <> 0 Then
        theString = Mid$(theString, i + Len(start_word))
        i = InStr(1, theString, end_word)
        If i <> 0 Then
            theString = Trim$(Left$(theString, i - 1))
        End If
    End If
    betweenWords = theString
End Function

?betweenwords("L6 - 9.3L 570ci DIESEL DI Turbo/Aftercooled/Intercooled type MaxxForce 10 - 4 valve SOHC", "type", "-")
MaxxForce 10

//this has Validation to check if the "start word" and "end word" exist on the target string.

 

[EzGoingKev]

//this has Validation to check if the "start word" and "end word" exist on the target string.

@arnelgp - thanks for the code.

I do not believe the validation is working correctly. I am running the function on field in a table using BetweenWords([FieldName], "type", "-"). Here are two examples.

Original string -> BetweenWords Result

L6 - 9.3L 570ci DIESEL DI Turbo/Aftercooled/Intercooled type MaxxForce 10 - 4 valve SOHC-> MaxxForce 10
L6 - 5.9L 359ci DIESEL DI Turbocharged vin 6 - 4 valve OHV -> L6 - 5.9L 359ci DIESEL DI Turbocharged vin 6 - 4 valve OHV

Since the second string above does not have the word "type" in it I would have thought the validation process would have skipped over it and come blank. Is my thinking incorrect?

 

[MajP]

change code to

Public Function betweenWords(ByVal theString As String, ByVal start_word As String, ByVal end_word As String) As String
    Dim i As Integer
    i = InStrRev(theString, start_word)
    If i <> 0 Then
        theString = Mid$(theString, i + Len(start_word))
        i = InStr(1, theString, end_word)
         betweenWords = theString
        If i <> 0 Then
            theString = Trim$(Left$(theString, i - 1))
             betweenWords = theString
        End If
   End If
    
End Function

 

[EzGoingKev]

@MajP - that did it. Thanks.

 

[arnelgp]

would have skipped over it and come blank

It will not skip. It will teturn the original string.

 

[EzGoingKev]

Thanks for the help with this.

So I am at point where I can understand some of the VBA and write a little on my own. If I need to do something I will google it, search here, etc. Sometimes I can find something will do exactly what I need it to do. Sometimes it does not do exactly what I need it to do but I can understand the code enough to modify it so it will. A lot of the stuff I have no clue about but I am learning.

One question I have is that a lot of the code I see has an "Error Handler" part of it. Typically something like this:

On Error GoTo ErrorHandler

Error_Exit:
    Exit Function

ErrorHandler:
    MsgBox Error$
    Resume Error_Exit

Some code does not. This code did not have it. Is it just personal preference thing or is there a "rule" based on what the code is doing to use an "Error Handler"?

 

[arnelgp]

then use code on post#24 (no need any validation, meaning if it fails, just return a blank string):

Public Function GetType(strIn As String, star_string as string, end_string as string) As String
On Error GoTo ErrorHandler
  GetType = Trim$(Split(strIn, start_string)(1))
  GetType = Trim$(Split(GetType, end_string)(0))

Error_Exit:
    Exit Function

ErrorHandler:
    'MsgBox Error$
    Resume Error_Exit
End Function

i commented out the Msgbox since the function will be used in query.
you don't want to get Msgbox on each records that fails causing the
query to pause for the msgbox.

 

[EzGoingKev]

OK thanks. I understand a little more now.

So if you put it a query and there is an issue with the data, like a blank field, an error message would pop up for every single instance. This would hold things up. Leaving it out the query runs and you would just get the #Error in the field and not hold things up.

 

[arnelgp]

if there are blank/null fields you need to change your function
so the function will only return blank string and not #Error:

Public Function GetType(strIn As Variant, star_string as string, end_string as string) As String
   strIn = strIn & ""
On Error GoTo ErrorHandler
  GetType = Trim$(Split(strIn, start_string)(1))
  GetType = Trim$(Split(GetType, end_string)(0))

Error_Exit:
    Exit Function

ErrorHandler:
    'MsgBox Error$
    Resume Error_Exit
End Function

 

[EzGoingKev]

if there are blank/null fields you need to change your function
so the function will only return blank string and not #Error:

Public Function GetType(strIn As Variant, star_string as string, end_string as string) As String
   strIn = strIn & ""
On Error GoTo ErrorHandler
  GetType = Trim$(Split(strIn, start_string)(1))
  GetType = Trim$(Split(GetType, end_string)(0))

Error_Exit:
    Exit Function

ErrorHandler:
    'MsgBox Error$
    Resume Error_Exit
End Function

That code works. There is a typo in the first line. star_string needs to be start_string.