Converting a concatenated string into a hyper link

[AndyRob1973]

Hello all,



I am trying to write a query(Access 2010) which concatenates two fields from a table to make a third field which needs to be a hyperlink that can be clicked on to take the user to a particular part of a webpage.

eg

COLUMN A= https://www.abc.com
COLUMN B = 123456
COLUMN C = [COL A]&'/'&[COL B]

So that the output reads https://www.abc.com/123456 and can be clicked on to take you to the relevant page.

It may be that I can do this as a calculated field in the original table?

Thankyou

[moke123]

You can use application.followhyperlink or better yet . . . http://access.mvps.org/access/api/api0018.htm

[AndyRob1973]

Thankyou!

So would I type in the access query something like:

application.followhyperlink([COL A]&'/'&[COL B])

[moke123]

You would concatenate the string in your query. In the double click event of the control on your form you would have

Application.FollowHyperLink Me.YourControlName

Many experienced developers avoid the hyperlink datatype and opt to do like above.

[AndyRob1973]

Thanks moke123,

I'll try that.

Could a similar thing be done in the query itself ie so that when you run the query, each line has a url link?

Or could it be done as a calculated field in the source table?

[moke123]

Could a similar thing be done in the query itself ie so that when you run the query, each line has a url link?

No. You should use a continuous form or subform to display the results of the query and set the double click event of the control to launch the URL.

Or could it be done as a calculated field in the source table?

Normally you would want to avoid using a calculated field in the table where the information is stored already.
You would concatenate it in the source query for the form or report as needed.

[AndyRob1973]

I have kind of managed it seems to only take me to the home page rather than the specific page in the URL.

(I do something similar n POWER BI dashboard and that allows me to go the specific page.

[moke123]

It should take you to whatever URL you have constructed.
try adding a debug.print [COL A]&'/'&[COL B] to make sure your getting it right.

[AndyRob1973]

I've checked the url quite a few times. When I cut and paste what I have constructed into the internet address bar it then opens the correct page.

I tested it in excel and the same thing happens, but in power bi it goes to the correct page! Bizarre!

[accesstos]

Check the attached sample (test the links in Form1), it works as expected.

[AndyRob1973]

Thankyou accesstos,

That's a really good example for me to work with. Unfortunately when I plug in the same details for the website I want to connect to , it takes me to the home page rather than the specific page in the url.

If I copy and paste my url into the browser it works fine. Have you any idea as to why this is?

[AndyRob1973]

Hi again,

I've scoured the net and found something that appears to work in the click event of 'Link'.

I have no idea how it works, so if anyone could explain it or improve on it, I'd be grateful!

Private Sub Link_Click()

Shell "explorer " & Chr(34) & Me.Link & Chr(34)

End Sub

[accesstos]

Probably, is an Internet Explorer issue, but, as you found something that works -go ahead!

You can use your favorite web browser with Shell. For example:

Shell "C:\Program Files\Mozilla Firefox\firefox.exe " & Chr(34) & Me.Link & Chr(34)

Cheers,
John

[moke123]

Check out the ShellExecute routine that I linked to in post#2 http://access.mvps.org/access/api/api0018.htm

Its not only good for urls but works with emails, files, folders, URLs and if there is an unknown extension will open the "Open with" dialog.