Split([fieldname],"\")(2)

[BrianPaul]

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.

I liked it but couldnt get it to work in the querry.
Exp1:Split([mybook],"\")(2)

Is there anything wrong with it..or do I need to write a function in a
module for it.? Thanks,,,,interesting topic

 

[Douglas J. Steele]

What happens when you run the query? Make sure that, unlike the sample I
gave you, you've got the correct slash in your Split function.

Oh, what version of Access are you using? There was an issue with using the
Split function in queries in early versions of Access 2000. If that's what
you're using, you might want to write a function to wrap around the Split
function:

Function GetSecondElement(InputValue As String) As String

GetSecondElement = Split(InputValue, "\")(2)

End Function

You'd then put

Exp1:GetSecondElement([mybook])

in your query.

On the other hand, is it possible that mybook is null in some cases?

 

[aaron.kempf]

split it for real and persist it in a database.

split it into each part; and store it in a database. it's a LOT faster
than searching through the whole big text and trying to split it on
query-time


-Aaron

 

[Albert D. Kallal]

Interesting..but, for some reason the split function cannot be used in a
query....

So, you have to use a public function smartin pointed out...

I am not really sure why sql does not support that function, but it seems
that the expression service gets confused
due to those brackets hanging on the end.......

 

[aaron.kempf]

you CAN utilize functions like split.. on the database side.. with SQL
2005 express.. it's free and it ROCKS

www.novicksoftware.com download the free fn_tab_split is what it's
called

and then with SQL 2005 you can use the CROSS APPLY function to apply
these types of UDFs in a query