• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • 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

\\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
snyperj
Asked:
snyperj
  • 2
  • 2
2 Solutions
 
peter57rCommented:
Dim arr
dim Myservername as string
dim MyUsername as string
arr= split(mid(yourfieldnamehere,3), "/")

MyServername = arr(0)
MyUsername = arr(3)
0
 
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.
0
 
peter57rCommented:
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
 
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)
0
 
snyperjAuthor Commented:
didn't need to do it afterall, but thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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