Hyperlink, Find and Replace

[kitty77]

I'm working with a hyperlink field. I know they can be tricky.
I'm trying to do a find and replace but it changes the format?
In the past, I had to export to excel but wondering if a better way exists.

\\server\testing\custom\12345.pdf
file:///\\server\testing\custom\.pdf (after replace)

 

[theDBguy]

Doesn't look like you're replacing anything but only adding something. Are you talking about using an UPDATE query? If so, can you please post the SQL statement? Thanks.

 

[kitty77]

Sorry... yes, I was simply replacing "normal" with "custom"

\\server\testing\normal\12345.pdf
file:///\\server\testing\custom\.pdf (after replace)

 

[kitty77]

this seems to keep the format. In a query. How can I convert that to vba for a command button?

Expr: Replace([Mlink1],"normal","custom")

 

[theDBguy]

this seems to keep the format. In a query. How can I convert that to vba for a command button?

Expr: Replace([Mlink1],"normal","custom")

You might want to test that first. Is it clickable?

 

[Pat Hartman]

Are you using the hyperlink data type?

 

[kitty77]

Something like this...

[Mlink4] = Replace([Mlink1],"normal","custom")

 

[kitty77]

You might want to test that first. Is it clickable?

Yes

 

[kitty77]

Are you using the hyperlink data type?

yes

 

[theDBguy]

this seems to keep the format. In a query. How can I convert that to vba for a command button?

Expr: Replace([Mlink1],"normal","custom")

Have you tried using the same expression in your VBA code? Show us what you have for the button.

 

[Pat Hartman]

The hyperlink data type holds two pieces of information. The actual link and a nice name. I never use that data type because it cannot be upsized to SQL Server so I can't tell you the syntax for referring to one part vs the other. If all you need to store is the link, change the data type to plain text. Then to open the document, use the FollowHyperlink method in the dbl-click event of the control.

 

[kitty77]

The hyperlink data type holds two pieces of information. The actual link and a nice name. I never use that data type because it cannot be upsized to SQL Server so I can't tell you the syntax for referring to one part vs the other. If all you need to store is the link, change the data type to plain text. Then to open the document, use the FollowHyperlink method in the dbl-click event of the control.

Got it.

 

[kitty77]

Have you tried using the same expression in your VBA code? Show us what you have for the button.

thought I did. Works now. Thanks!

 

[theDBguy]

thought I did. Works now. Thanks!

Okay, glad to hear you got it sorted out. Cheers!