Find and Replace

[kitty77]

I have a hyperlink field that has some errors. I would like to clean it up.
I created another field (text) and copied all the data into that field. I'm trying to do a find and replace but it does not find the #

#file:///A:\TEST\Options\42044.pdf# I want to fix it to A:\TEST\Options\42044.pdf

I'm guessing it has something to do with the #.

 

[arnelgp]

you can use Update Query to replace it:

Update customers Set [pdf link] = Replace$(Replace$([pdf link], "#", ""), "file:///", "")

 

[kitty77]

Can you send me an example? Not sure how to set it up.
Thanks!

 

[kitty77]

But why does it show up in the table with the file:///?

 

[kitty77]

you can use Update Query to replace it:

Update customers Set [pdf link] = Replace$(Replace$([pdf link], "#", ""), "file:///", "")

I created a query but it gives me an error? Replace$

 

[jdraw]

What error? Try Replace (without the $)
Please tell us more about the hyperlink. Where does it reside? What is the major issue?...

 

[kitty77]

It resides in a table. The issue is that it looks a little strange as a hyperlink, file:///A:...

 

[kitty77]

I have some that look one way and some other ways... But both hyperlinks (views) works.
When you click edit hyperlink, both views show it without the file:///

 

[jdraw]

When you click on the hyperlink, does it display the pdf?

 

[kitty77]

Yes either way. Just like it to look and be uniformed. Access seems to like the file:/// way.
Just looks cleaner the other way.

 

[isladogs]

Consider just using a text field to store the file paths. It avoids the issues that people often have with hyperlink fields.

 

[kitty77]

Yes but then they can't click it as a hyperlink?

 

[Pat Hartman]

You can add code to the double click event that uses the FollowHyperlink method to accomplish that. I would not use the click event because that interferes with maintenance. If the links come from some other place so the user never has to update them in the form, then the click event will be fine. You can set the format of the control so that it looks like a hyperlink.

 

[kitty77]

I'm using the hyperlink as an export to html, outside of access.

 

[Pat Hartman]

The FollowHyperlink method works for any file type that is registered with Windows. It will open web pages, email programs, word, excel, powerpoint, jpg, .pdf, etc.

 

[kitty77]

I see, can you send me an example?

 

[isladogs]

Application.FollowHyperlink "https://isladogs.co.uk"

Application.FollowHyperlink "c:\MyFiles\readme.txt"

 

[Boutros55]

Yes but then they can't click it as a hyperlink?

An alternative to the double click option is having a button to use the FollowHyperlink method.

 

[Pat Hartman]

If no one needs to edit the hyperlink on the form, you can use the click event rather than the double-click event to activate the hyperlink.