One of the tables in my database is linked to a SharePoint document library, so the table contains a "Name" field which holds a hyperlink pointing to each file in the document library. The hyperlink text is the file name with extension (Document1.docx). Another field in the table is named "Encoded Absolute URL", which contains the entire URL used to get to the file, which is the same as the actual URL from the Name field. In the query I would like to remove the hyperlink text from the name field from the Encoded Absolute URL, resulting in a URL leading to the folder where the file is stored. I could do this with Replace() if it was possible to copy the hyperlink text. Could anyone tell me how to copy just the hyperlink text from from within the query builder?

Hi,

Are you trying to make it so that the result of the query is not a hyperlink? If so, you might have to create a function for that.

Just my 2 cents...

Either so the result of the query is not a hyperlink, or a separate text field is created that matches the hyperlink text (and is not a hyperlink).

Another option would be to locate the last forward slash in the field and select all text prior to it, but I'm not sure how to do that from within a query either.

I'm trying to avoid using VBA on this project because I'd like to simplify moving everything to SharePoint later.

Even if I could get the length of the hyperlink text I could get it to work from within the query, but Len([Name]) is returning the length of the underlying URL, not the text itself.

I have to say that I don't understand well what you are trying to do.
This line is the most confusing:

Now what happen if you use the CStr() function???

Hi snekker,

Our SharePoint server is down right now so I can't do any testing but if we're talking about one Document Library here, then you should just be able to enter the URL in the query (because you know what it is, correct?).

Just my 2 cents...

I think I may have found a workaround using some of the other SharePoint-created fields: "URL Path" and "Path".

[URL Path] looks like "/folders/myfolder/myfile.pdf"
[Path] looks like "/folders/myfolder"

A query with the following Replace() left me with just the file name:
FileName: Replace(Replace([URL Path],[Path],""),"/","")

Then I based another query off the first to get the folder:

Folder: Replace([Encoded Absolute URL],[FileName],"")

The result was a column containing the URL leading to the folder where the file is stored (http://www.fileserver.com/folders/myfolder/)

Hi snekker,

Glad to hear you got it sorted out but why would you need two separate queries for it. You should be able to nest the Replace() functions together.

Just my 2 cents...

I thought about doing it that way, but there are several folders and subfolders inside the document library and I want the user to be able to navigate the folder immediately containing their file. Additionally, the backup server has a slightly different URL so a fixed URL wouldn't work if the first server was down for maintenance. I guess none of the linked tables will work anyway unless the linked table manager is run, but at least I won't have to redo the queries. Sorry, I know this doesn't make a lot of sense... probably why I wasn't getting much back from search results. Thanks for looking in to it.

Hmm, I might have to try that. I used two just because once I got the first one working I didn't want to change anything for fear of messing up a good thing. Guess that's why "copy the database first" was invented.

Hi snekker,

Good luck with your project.

Cheers