Extract Portion of Text String

  • Thread starter Thread starter Bre-x
  • Start date Start date
B

Bre-x

hi,
i have text field with these values

D:\Docs\Acounting\

i need to separate that value into:

D
Docs
Accounting

Thnk you all

Bre-x
 
Assuming you're using Access 2000 or higher, you can take advantage of the
Split function to break your string into array elements every slash:

Dim strSplitWords() As String
Dim strWord As String

strWord = "D:\Docs\Acounting\"
strSplitWords = Split(strWord, "\")

Each element of strSplitWords will be separated now: strSplitWords(0) will
be D:, strSplitWords(1) will be Docs, strSplitWords(2) will be Acounting.
(You'll also have a strSplitWords(3), which won't contain anything, because
of the final slash, but you can ignore it)
 
thank you sir

Douglas J. Steele said:
Assuming you're using Access 2000 or higher, you can take advantage of the
Split function to break your string into array elements every slash:

Dim strSplitWords() As String
Dim strWord As String

strWord = "D:\Docs\Acounting\"
strSplitWords = Split(strWord, "\")

Each element of strSplitWords will be separated now: strSplitWords(0) will
be D:, strSplitWords(1) will be Docs, strSplitWords(2) will be Acounting.
(You'll also have a strSplitWords(3), which won't contain anything, because
of the final slash, but you can ignore it)
Click to expand...
 
hi,
i have text field with these values

D:\Docs\Acounting\

i need to separate that value into:

D
Docs
Accounting

Thnk you all

Bre-x
Click to expand...

See the online help for the Split() function in the VBA editor.

Might you have

E:\DepartmentK\Docs\Sales\Kevin\Current

i.e. can you be certain that there are only two levels?
 
Sir,
it does not work, i must have done something wrong.
here is my function

Function fl(strWord As String) As String
fl = Split(strWord, "\")
End Function

I use it on a query

SELECT Main.FilePath, fl([FilePath]) AS Expr1, Main.FileName, Main.FZ,
Main.FilePath
FROM Main;

Error:
Run-time error '13':
type mismatch


thank you

Bre-x
 
Yes, you didn't implement it properly.

The Split function returns an array. Your fl function is defined as being a
string, not an array. You can't assign an array to a string.

You could define the function as a variant

Function fl(strWord As String) As Variant
fl = Split(strWord, "\")
End Function

That will get rid of the Type Mismatch error. However, it still won't
properly in your query, as each value returned in a query must be scalar,
not an array.

If you know that there will always be 3 levels, as in your example, try
changing the function as I described and changing the query to:

SELECT Main.FilePath, fl([FilePath])(0) AS Drive, fl([FilePath])(1) AS
Folder1, fl([FilePath])(2) AS Folder2, Main.FileName, Main.FZ, Main.FilePath
FROM Main;

If that's not what you want, you'd better post back with more details about
exactly what it is you're trying to accomplish.
 
Back
Top