• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

Need help separating data in a string

I have thousands of records in a single field within a table in the following format


The actual data has different values for SERVERNAME AND USERNAME then shown above. I would like to extract the SERVERNAME  and USERNAME values into their own fields.

How to do?
  • 2
  • 2
2 Solutions
Dim arr
dim Myservername as string
dim MyUsername as string
arr= split(mid(yourfieldnamehere,3), "/")

MyServername = arr(0)
MyUsername = arr(3)
snyperjAuthor Commented:
Sorry should have been more specific on what I was looking for.  I created a 'server' and a 'username' field in the table... so I was just looking for how to populate this with an update query run against the table.
Depends on which version of Access you have.

For the latest couple of versions ...

Update tablename set [server] =  split(mid(yourfieldnamehere,3), "/")(0),
[username] =split(mid(yourfieldnamehere,3), "/")(3)
Rey Obrero (Capricorn1)Commented:
try this update query, change tablex and [fieldName] accordingly

update tablex
set server=Mid([fieldName], 3, InStr(3, [fieldName], "\") - 3)
, username=Mid([fieldName], InStrRev([fieldName], "\") + 1)
snyperjAuthor Commented:
didn't need to do it afterall, but thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now