Hyperlink in MS Access report

Question

I've made an MS Access 2013 database to keep track of all communications regarding a trading website. The tables and columns relevant to this question are Advertisements with columns ID (Number) and Link (Hyperlink), and Notes with column Advertisement, which contains an Advertisement ID. The Link field contains an http link to the advertisement on the website.

It is easy to include the Link column in reports, but to save space, I would like to turn the ID field into a hyperlink with the ID as displayed text and the contents of the Link column as the target. How would I go about that?

I already played a little with the properties of the ID column and set "Is Hyperlink" to true and "Hyperlink target" to "SELECT '#' & Link & '#' AS URL FROM Advertisements", but this didn't work. It may need a WHERE clause, but how would I refer to the value of the ID field of the record in question?

Answer 1

To illustrate, I created a table named [LinkTest] with columns

ID - AutoNumber, Primary Key
SiteName - Text(255)
SiteURL - Hyperlink

and data

ID  SiteName        SiteURL                    
--  --------------  ---------------------------
 1  Stack Overflow  #http://stackoverflow.com/#
 2  YouTube         #http://www.youtube.com/#  

I created a new Report with a Text Box for the URL

and it displayed the URL as expected.

To display the SiteName as a hyperlink I just edited the control source to prepend the site name to the #-delimited hyperlink text

and the Text Box now displays the SiteName as a link to the URL...

... because the Text Box (where the hand cursor is pointing) now contains

Stack Overflow#http://stackoverflow.com/#