Convert If InStr Excel VBA to Access Expression?

[rhaas128]

I have the below code in Excel VBA. I was wanting to try and put this into Access as an expression perhaps? The goal is to look at each record in the table and parse out the Access Profile Name. Anyone able to help me out?

Dim R As Long, Data As Variant
  Data = Range("G2", Cells(Rows.Count, "G").End(xlUp))
  For R = 1 To UBound(Data)
    If InStr(Data(R, 1), "Access Profile Name : ") Then
      Data(R, 1) = Split(Split(Data(R, 1), "Access Profile Name : ", , vbTextCompare)(1), vbLf)(0)
    Else
      Data(R, 1) = "AP Name Unknown"
    End If
  Next
  Range("J2").Resize(UBound(<wbr>Data)) = Data

 

[Joe4]

Can you post a few example of sample data with expected output?

 

[rhaas128]

Can you post a few example of sample data with expected output?

Description	Expected Output
Request: NeededAccess Access Number : 01 Access Profile Name : ANAME Business Justification: New hire for AKFE.	ANAME
re-add previous access	No Match
Line 1 Blah Line 2 Blah Request: NeededAccess Access Number : 01 Access Profile Name : ANAME Business Justification: New hire for AKFE.	ANAME

<tbody>
</tbody>

 

[Joe4]

So, are you going to have the whole entire block of text like that in a single field?
Are there soft-carriage returns in there like that?
What is the Data Type of the fields holding this data, Text or Memo?

 

[rhaas128]

It is a memo. The actual data looks like this. There are 6 spaces between each new line or what a new line would be in excel.

Request: NeededAccess Access Number : 01 Access Profile Name : ANAME Business Justification: New hire for AKFE. <tbody> </tbody>	ANAME

<tbody>
</tbody>

 

[Joe4]

For lines where an Access Profile Name exists, will it ALWAYS be immediately followed by "Business Justification" (ignoring all the extra spaces)?

 

[rhaas128]

Not always. It may have other lines as well.

 

[Joe4]

Are there are rules regarding the length of this Profile Name?
Just trying to figure out the best way of attacking this. Trying to pull something out of a bunch of text can be a little tricky.

 

[rhaas128]

Not that I know of. We have some as short as 4 characters and others upwards of 30. I don't mind the questions and greatly appreciate the help!

 

[Joe4]

For something this complex, I like to create my own User Defined Functions in VBA.
Here is what I came up with:

Function AccessProfileName(myString As Variant) As String

    Dim myHeader As String
    Dim myStart As Long
    Dim myTemp As String
    Dim mySpace As Long
    
'   Enter header to look for
    myHeader = "Access Profile Name : "
    
'   Find where header starts
    myStart = InStr(myString, myHeader)
    
'   If no header found, return "No Match"
    If myStart = 0 Then
        AccessProfileName = "No Match"
    Else
'   ...else pull everything after the header
        myTemp = Trim(Mid(myString, myStart + Len(myHeader), 100))
'       Then find first space of the new string
        mySpace = InStr(myTemp, " ")
'       If no spaces, just return the string...
        If mySpace = 0 Then
            AccessProfileName = myTemp
        Else
'       ...else return everything up to that first space
            AccessProfileName = Left(myTemp, mySpace - 1)
        End If
    End If
    
End Function

So then you would just use this like any other function in a Query, Report, VBA, etc., i.e.
AccessProfileName([YourMemoField])