Search code examples
ms-access

MS Access mid and instr functions

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

Solution

  • 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