Make Derived Hyperlink Clickable in a query

[sxschech]

I have a query that is reading data from a table. One of the fields contains a hyperlink. When I run the query, the hyperlink is visible and I can click on it and it works fine. What I would like to do, is add some additional cols to the query. Since the only part of the hyperlink to change is the file name, I was hoping I could create an expression so that I don't have to do a make table and store additional data in a table. When I run the query, the information looks okay, but is not clickable. Is it possible or will I end up needing to run this as a make table first? Here is the expression:

MNDoc: replaceqry(Left([location],instrrevqry([location],"\")) & "Meeting Notice_" & Mid([location],instrrevqry([location],"\")+1),"pdf","doc")

Datasheetview shows:
Location MNDoc
H:\Files\Document042204.pdf #H:\Files\Meeting Notice_1.doc#

By way of explanation...using Access 2000. Since wasn't able to use replace and instrrev functions directly in query, created functions to run them which is why replace function is showing as replaceqry in the above expression.

 

[sxschech]

Found a solution to the hyperlink problem. Instead of doing the link directly in the query, it needs to be done on a form and then using code, it will simulate a hyperlink.

Create a text box on the form
Put the replaceqry expression into the control source

=replaceqry(Left([location],instrrevqry([location],"\")) & "Meeting Notice_" & Mid([location],instrrevqry([location],"\")+1),"#","")

and then in the Click event

Private Sub Text3_Click()
     Application.FollowHyperlink Me.Text3
End Sub

Text3 could contain a path or url