Instr text from a string

[oxicottin]

Hello, I have a long string that I need to retrieve and display 2 parts of the text. Can someone tell me how to go about this?

Report Runtime: 07-JUL-2021 06.06.00 AM SCM_Test_Supply_Demand_Analysis - Test Supply Demand Analysis Item Number : '%' , Planner Code : '%' , Days Out : '10' , Organization Name : 'Something - 785' , Item Type : '%'

I need to retrieve and display the date/time and the Days Out in my report from this jumbled mess.

Report Runtime: 07-JUL-2021 06.06.00 AM
AND
Days Out : 10

 

[Minty]

Can you confirm that "SCM_Test_Supply_..." Always follows the Runtime date and time?

 

[theDBguy]

Hi. For some reason, every time I hear someone needs to extract a part of a string, I immediately think about using regex.

 

[oxicottin]

The way its written is always the same except the date and time and the days out number will change.

 

[Gasman]

Hi. For some reason, every time I hear someone needs to extract a part of a string, I immediately think about using regex.

And by the time I got the pattern correct, I could have extracted the data with Instr(), Left() & Mid()

 

[Minty]

Based on your string being in a field called MyStringField the following will work

Function fnGetDaysOut(sStringIn As String) As String

    Dim iStartPos As Integer
    Dim iEndPos   As Integer
  
    iStartPos = fnWordStart(sStringIn , "Days Out", 1)
    iEndPos = fnWordStart(sStringIn , ", Organization ", 1)
  
     fnGetDaysOut = Trim(Mid(sStringIn, iStartPos, iEndPos - iStartPos))
     fnGetDaysOut = Replace(fnGetDaysOut, "'", "")
    

End Function

Function fnWordStart(sString As String, sSearch As String, Optional iOccur As Long = 1) As Long

    Dim icount  As Long
    Dim iPos    As Long
  
    icount = 0
    iPos = 1
    While icount < iOccur
        iPos = InStr(iPos, sString, sSearch)
        icount = icount + 1
        If icount < iOccur Then iPos = iPos + 1
    Wend
    fnWordStart = iPos

End Function

In your query use
DaysOutText: fnGetDaysOut([MyStringField])

Now I think I'm going to let you build the one to get the first part, as it's national learn how to code day.
(I may have made that up)

 

[oxicottin]

@Minty, thank you the days out works and with it being code day Ill get the Report Runtime

Function fnGetRuntime(sStringIn As String) As String
'Purpose: to get date and time report was ran
'Use: Runtime: fnGetRuntime([TableFieldName])

    Dim iStartPos As Integer
    Dim iEndPos   As Integer
 
    iStartPos = fnWordStart(sStringIn, "Report Runtime", 1)
    iEndPos = fnWordStart(sStringIn, " SCM_Test_Supply_Demand_Analysis ", 1)
 
     fnGetRuntime = Trim(Mid(sStringIn, iStartPos, iEndPos - iStartPos))
     fnGetRuntime = Replace(fnGetRuntime, "'", "")
    
End Function