extracting midtext from field

pma111
pma111 used Ask the Experts™
on
Is there an easy way in access, to extract text mid-string and populate it into a new field in a table. I have a table with an excess of 1,000,000 rows, and one field (FullName), is essentially an UNC path from a file server, e.g. \\server\share\username . I basically need to extract the username section from that into a separate field as I need to do some group by and min/max stats on the field. There is a slight inconsistency in that some rows will have additional data in the path e.g. \\server\share\username\folder\doc1.docx, whereas at the start the rows are just \\server\share\username  - its basically a recurse file inventory which would start with the usernames, then a more detailed breakdown of the files held within each simultaneously afterwards - but either way, I need to extract the username 'segment' from the string on every single row, regardless of format and wondering how best to do so.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
If its always
\\server\share\username then the following command will give what you need

split("\\server\share\username","\")(4)

Open in new window

Author

Commented:
Sorry no not really, the username will be unique to each users data, e.g. \\server\share\john, \\server\share\Stephen, \\server\share\carl etc. or possibly \\server\share\john\folder\doc1.docx
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
What John gave you would still work as long as it's always \\server\share\username

What comes after that doesn't matter.

Jim.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

John TsioumprisSoftware & Systems Engineer

Commented:
Maybe i should have rephrase it
If your string has the structure
\\server\share\Someusername
the using the Split command will give that "SomeUserName"
with the code i posted
split("\\server\share\SomeUsername","\")(4)
Split will count the occurrences of "\" and return the 5th part of text between all the "\"

Author

Commented:
Ok thanks, I am not overly familiar with this function. How would it fit into a query, or where exactly would you execute this function, would it need to be part of some VBA code, or can it be run in the same way a standard SELECT query? I am struggling to see where exactly you'd specify the field in which to apply to split function.
Software & Systems Engineer
Commented:
In a query as it is you can't...you have to put it in a simple public function and use that for the query
Public function getMidText(InputString as String) as String 
getMidText = split(InputString,"\")(4)
End Function

Open in new window

And in your query
Select getMidText(SomeField) From yourTable

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
John's recommendation should work, although you cannot use that directly in a query, you will have to create a function;
Public Function fnUserName(varPath as Variant) as Variant

    On Error goto ProcError

    if IsNull(varPath) then
        fnUserName = NULL
    Else
        fnUserName = Split(varPath, "\")(4)
    end if

    Exit Function

ProcError:
    fnUserName = NULL

End Function

Open in new window

Then call this in your query:
SELECT fnUserName([Path]) as UserID, Min(SomeField), Max(SomeField)
FROM yourTable
GROUP BY fnUserName([Path])

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial