MS Access mid and instr functions-CodePudding

I am trying to find a file path within a larger string of event data. The file path and names vary in length for each event. Example:

Security ID: xxxx Account Name: xxxx Accound Domain: xxxx Object Name: c:\temp\MyFile.doc Handle ID: xxxx Resource Attributes: xxxx

I want the file and path only = c:\temp\MyFile.doc so I used Mid and instr to get the string between Object Name: and Handle ID. It works except it still leaves some characters after the file name. Below is my function.

Mid(event,Instr(event,"Object Name: ")   13, InstrRev(event,"Handle ID:") - Len(mid(event,InstrRev(event,"Handle ID:"))))

Thank You

CodePudding user response：

You could use Split twice:

Text = "Security ID: xxxx Account Name: xxxx Accound Domain: xxxx Object Name: c:\temp\MyFile.doc Handle ID: xxxx Resource Attributes: xxxx"
Path = Split(Split(Text, "Object Name: ")(1), " Handle ID:")(0)

Debug.Print Path

c:\temp\MyFile.doc