?
Solved

Need help separating data in a string

Posted on 2013-06-26
5
Medium Priority
?
208 Views
Last Modified: 2013-06-28
I have thousands of records in a single field within a table in the following format

\\SERVERNAME\C$\DOCUMENTS AND SETTINGS\USERNAME

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?
0
Comment
Question by:snyperj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 39278543
Dim arr
dim Myservername as string
dim MyUsername as string
arr= split(mid(yourfieldnamehere,3), "/")

MyServername = arr(0)
MyUsername = arr(3)
0
 

Author Comment

by:snyperj
ID: 39278585
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.
0
 
LVL 77

Accepted Solution

by:
peter57r earned 1000 total points
ID: 39278634
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)
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 39278827
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)
0
 

Author Closing Comment

by:snyperj
ID: 39285364
didn't need to do it afterall, but thanks.
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question