The Mid function returns a substring of characters from another string. The
syntax is Mid(string, start[, length]), where:
string is the string expression from which characters will be returned
start is the position in string at which the part to be taken begins
length is the number of characters to return. (Length is optional: if
omitted, all characters from the start position to the end of the string are
returned
Since C:\EBooks\ is 10 characters, you want to start extracting at position
11 in the string.
The InStr function returns the position of the first occurrence of one
string within another. The syntax being used here is InStr(start, string1,
strinrg2), where:
start is a numeric expression that sets the starting position for each
search
string1 is the string expression being searched
string2 is the string expression being sought.
InStr(11, [fieldname], "\") - 11) says to find the first occurrence of \ in
the string after position 11. In the string C:\Ebooks\Web Design\ASP Dot NET
Web Developer's Guide.pdf, the first \ after position 11 occurs at position
21. Since you only want what's between positions 11 and the result of InStr,
you subtract 11 from the call to InStr.
Overall, I like Albert's solution better. In case you missed it, it was:
Split([fieldname],"\")(2)
The Split function returns a zero-based one-dimensional array of substrings.
The syntax is Split(expression, delimiter), where:
expression is the string expression contains substrings and delimiters
delimiter is a string character used to identify substring limits.
In other words, what the Split function is doing is creating a bunch of
substrings, breaking at each \. Split("C:\Ebooks\Web Design\ASP Dot NET Web
Developer's Guide.pdf", "/") will create an array with 4 elements in it.
Element 0 will be "C:", element 1 will be "Ebooks", element 2 will be "Web
Design", and element 3 will be "ASP Dot NET Web Developer's Guide.pdf". The
(2) in Split([fieldname],"\")(2) says only return the 2nd element.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
BrianPaul said:
John
Mid([fieldname], 11, InStr(11, [fieldname], "\") - 11) does work fine.
However, I would like to understand the syntax. I know Fieldname is the
field.
What confuses me is the Mid, Instr (guessing is a function) and expecially
why in this case are we using the 11 with the mid function and guessing
Instr
function -11.
C:\Ebooks\Web Design\ASP Dot NET Web Developer's Guide.pdf
If I know how the above works, then I can apply it toward other databases.
Thanks, Just didnt want a giveme but trying to understand the solution.
John Vinson said:
On Tue, 8 Aug 2006 12:00:01 -0700, BrianPaul
from a table or querry: Books is the field. based on the table MyBooks
Querry Result:
C:\Ebooks\Web Design\ASP Dot NET Web Developer's Guide.pdf
C:\Ebooks\Msdn Training\ASP.NET - Introduction.pdf
I would like to return the result:
Web Design
MSDN Training
When i run the querry:
I have tried Trim. LTrim R.Trim, Mid, etc
however they deal with numbers of spaces etc after the function.
is there anyway this can be done to extract the string that I want?
Thanks
Do ALL of the records start with C:\EBooks\ (now and in the future)?
If so you can use the Left() and Instr() functions:
Mid([fieldname], 11, InStr(11, [fieldname], "\") - 11)
If the start of the field to be extracted might be at some other
position - maybe two or three subdirectories down - the problem gets
more complex of course!
John W. Vinson[MVP]
