Extracting Text From A Field

[EzGoingKev]

I have text like this:

L6 - 5.9L 359ci DIESEL DI Turbocharged vin C - 4 valve OHV
V8 - 6.6L 6599cc 403ci DIESEL MFI Turbocharged vin L type LGH - 4 valve OHV

I want to extract the VIN info - just the letters in bold. In these examples they are both single letters. Sometimes they can be a number. Sometimes there can be more than one character.

I tried using the code here and modified it to:

Mid([FieldName],InStr(1,[FieldName],"vin")+4,InStr(1,[FieldName]," ")-InStr(1,[FieldName],"vin")-4))

And I get an "Invalid procedure call " error. I have played around with it some but cannot get it to work.

Any help with this?

 

[theDBguy]

Hi. Try using the Split() function.

 

[jdraw]

Here's a function that should do what you ask.

Function KevOct21(strIn As String) As String
    Dim arr As Variant
    Dim i As Integer
        arr = Split(strIn, " ")
        For i = LBound(arr) To UBound(arr)
            If arr(i) = "vin" Then
                KevOct21 = arr(i + 1)
                Exit For
            End If
        Next i
End Function

Test routine:

Sub testkev()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("tblKev")
    Do While Not rs.EOF
        Debug.Print KevOct21(rs!field1)
        rs.MoveNext
    Loop
End Sub

This is my TblKEV

TblKev TblKev

ID	field1
1​	V8 - 6.6L 6599cc 403ci DIESEL MFI Turbocharged vin L type LGH - 4 valve OHV
2​	L6 - 5.9L 359ci DIESEL DI Turbocharged vin C - 4 valve OHV

 

[arnelgp]

Left(Mid([FieldName],InStr(1,[FieldName],"vin ")+4),InStr(1,Mid([FieldName],InStr(1,[FieldName],"vin ")+4)," ")-1)

 

[EzGoingKev]

Hi. Try using the Split() function.

@theDBguy - I googled the Split function and tried using their examples and all I got was errors. Would you please provide an example of how to use the Split function in this scenario?

@jdraw - Thanks for the function. I used arnelgp's statement but I guess if the code could be modified so that I could use a statement like KevOct21([FieldName], "vin", " ") I could get more use out of that.

Left(Mid([FieldName],InStr(1,[FieldName],"vin ")+4),InStr(1,Mid([FieldName],InStr(1,[FieldName],"vin ")+4)," ")-1)

@arnelgp - Thanks, that worked great!

 

[jdraw]

Kev,

Adjust as suggested:

' ----------------------------------------------------------------
' Procedure Name: KevOct21
' Purpose: Find string within string
' Procedure Kind: Function
' Procedure Access: Public
' Parameter strIn (String): string to be parsed
' Parameter sLookfor (String): string to be found
' Parameter sDelimiter (String): delimit character
' Return Type: String
' Author: Jack
' Date: 14-Oct-21
'
'https://www.access-programmers.co.uk/forums/threads/extracting-text-from-a-field.319910/
' ----------------------------------------------------------------
' ----------------------------------------------------------------
Function KevOct21(strIn As String, sLookfor As String, sDelimiter As String) As String
          Dim arr As Variant
          Dim i As Integer
10            arr = Split(strIn, sDelimiter)
20            For i = LBound(arr) To UBound(arr)
30                If arr(i) = sLookfor Then
40                    KevOct21 = arr(i + 1)
50                    Exit For
60                End If
70            Next i
             
          
End Function

test routine

Sub testkev()
          Dim rs As DAO.Recordset
10        Set rs = CurrentDb.OpenRecordset("tblKev")
20        Do While Not rs.EOF
30            Debug.Print KevOct21(rs!field1, "vin", " ")
40            rs.MoveNext
50        Loop
End Sub

 

[theDBguy]

@theDBguy - I googled the Split function and tried using their examples and all I got was errors. Would you please provide an example of how to use the Split function in this scenario?

Hi. Glad to hear you already have a solution. But since you asked, this is what I had in mind earlier.

Left(Split([FieldName], "vin ")(1), InStr(Split([FieldName], "vin ")(1), " ")-1)

 

[EzGoingKev]

@jdraw - Thanks. That works great. I can get A LOT of use out of that.

@theDBguy - Thanks for the examples. I was trying just the split part. I figured more to it so that is why I asked.

 

[EzGoingKev]

Hi. Glad to hear you already have a solution. But since you asked, this is what I had in mind earlier.

Left(Split([FieldName], "vin ")(1), InStr(Split([FieldName], "vin ")(1), " ")-1)

@theDBguy - I tried that statement and received the following error message:

"The expression you entered has an invalid . (dot) or ! operator or invalid paranthesis.
You may have entered an invalid identifier or typed parentheses following the Null constant."

When I close the error message the bold text is where it shows the issue is:

Left(Split([FieldName], "vin ")(1), InStr(Split([FieldName], "vin ")(1), " ")-1)

 

[Gasman]

Works for me?

Sub testSplit()
Dim fld As String
fld = "L6 - 5.9L 359ci DIESEL DI Turbocharged vin C - 4 valve OHV"
Debug.Print Left(Split([fld], "vin ")(1), InStr(Split([fld], "vin ")(1), " ") - 1)
End Sub

 

[EzGoingKev]

I am trying to use it in a query. Does the Split function only work in VBA?

 

[Gasman]

Yes, I believe Split is not recognised in a query. So create a function using that Split to return what you need.
In fact you were given that already in post 3 and arnelgp supplied another method in post 4 ?

 

[theDBguy]

I am trying to use it in a query. Does the Split function only work in VBA?

Sorry, that's correct. You can't use Split() in a query.

 

[EzGoingKev]

Well I guess that is why I had so many problems with it.

 

[Gasman]

Sorry, that's correct. You can't use Split() in a query.

Query was never mentioned?, or if it was I completely missed it?

 

[theDBguy]

Well I guess that is why I had so many problems with it.

In a query, just use @arnelgp's solution.

 

[EzGoingKev]

Query was never mentioned?, or if it was I completely missed it?

This is posted in the Queries subforum so I figured it was implied.

 

[Gasman]

Sorry, I just look at What's New.
I take some consolation in that I was not the only one, else split would not have been offered.?

 

[EzGoingKev]

No worries.

 

[EzGoingKev]

@jdraw - I ran into something this morning and cannot seem to figure it out.

Here is an example of the data I am working with:

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

I want to extract the "MaxxForce 10" from it so in a query I used:

EngDesg : ExtractString([EngineFullDetail], "type", "-")

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