Help with changing path names in hyperlink (200)

Topic

New Reply

Our server has just been swaped over and l cannot find a way of changing path names (these are in hyperlink data type format)
What l would like to do is to replace the words PANTHER with LEOPARD. I have tried to change the data type to text but this did not work.
Any help would be appreciated.

Reply | Quote

Replies

I haven’t heard of Access 200. Is it Access 2000, 2002 (XP) or 2003?

Reply | Quote

my mistake access 2000

Reply | Quote

1) Activate the Modules section of the database window.
2) Click New.
3) Copy the following code into the module window:

Function MyReplace(varText As Variant, Find As String, What As String) As Variant
If IsNull(varText) Then
MyReplace = Null
Else
MyReplace = Replace(varText, Find, What)
End If
End Function

4) Switch back to Access.
5) Activate the Queries section of the database window.
6) Create a new query in design view.
7) Add the table with the hyperlink field to the query.
8) Add the Hyperlink field to the query grid.
9) Select Query | Update Query; this adds a row ‘Update to’ in the query window.
10) Enter the following expression under Hyperlink in the ‘Update to’ row:

MyReplace([Hyperlink],"PANTHER","LEOPARD")

11) Select Query | Run.
12) You should see a message that you are about to update … rows. Click Yes.

Reply | Quote

i have managed to changed the paths by replacing the hyperlink to text format, but for some odd reason the end of the hyperlink has been chopped and the link is invalid.
see screenshot for what l mean.

Reply | Quote

See my previous reply for a way to update the hyperlink field through a query.

You shouldn’t change a hyperlink field to a text field, the values will be truncated to the length of the text field, with a maximum of 255 characters. You can, however, change it to a Memo field, then replace PANTHER with LEOPARD, and finally change it back to a Hyperlink field.

Reply | Quote

Thats brilliant Hans,

This will save me an awful lot of time.
I get an error message, see attachment.

Reply | Quote

Specify the name of the table in the expressions, e.g.

MyReplace([Beoing DRLs subtable].[Hyperlink],”PANTHER”,”LEOPARD”)

Reply | Quote

That works great thanks.
You saved me a lot of time , l really appreciate that help.

Reply | Quote

I will try this way if l cannot get the query to work.

Reply | Quote

Shall l send you a zipped up version of the database?
Once l know how it can be done with one table and l can do the rest.

Reply | Quote