Query to create a calculated hypertext field

[John Schneider]

I have an Access 2002 table with fields containing web links (in text
format). I need the field in this DataType so the it can be sorted and used
to exclude duplicate records with the same link, therefore I don't want the
DataType to be hyperlink. Also, it's considerably less difficult to update
or correct the link as textual data than when it's formatted as a hyperlink.

What I do want is to query the table and create a hyperlink DataType field
of the textual data so the link can be tested and/or accessed. I did this
before easily, after researching for hours, but lost my code and can't
remember how I did it. Any help??

John Schneider
02:40:32 AM Tue 10/11/05

 

[Allen Browne]

See:
Introduction to Hyperlink fields
at:
http://allenbrowne.com/casu-09.html

In essence, the hyperlink consists of the display value, hash, address,
hash, so you could use:
[Text1] + "#" + [Text1] + "#"

FollowHyperlink will help with jumping to the link.

 

[John Schneider]

Thanks for your quick reply....

I forgot to mention that I was aware of the format of a hyperlink, the
problem was getting the field to ACT LIKE a hyperlink. What I had hoped to
be a single step process, of simply creating a query with a calculated field
with a hyperlink property was not possible.

The only way I was able to get the link field to function was to create a
FORM based on the query and set the "IS HYPERLINK" property of any field(s)
that contain links to "yes".

In Access the following code in the query creates the hypertext formatted
data for the field:

play1: "Play1" + "#" + [Song Url-1] + "#"

Play1 is the name of the calculated field and also the text that appears in
the hyperlinked field.
[Song Url-1] is the name of the field that contains the text format of the
link. In this particular database there are three different links to the
same song so there is a "Play" button to test each link.

Unfortunately, running the query with the above code DOES NOT create an
active link. In order to make the link function, you also must also create
a form utilizing the query as it's data source and then set the "is
hyperlink" property to 'yes' for each hyperlink field. Viewing the form as
a datasheet results in the same display functionality as the query with the
added benefit of the link fields actually functioning.

In Excel, the function HYPERLINK(A2,"Play") will create an active link based
upon the text in cell A2. The word "Play" becomes the display_text in the
hyperlink cell. I had confused myself, thinking that I had accomplished the
same simple process in Access. I was hoping to find a function in Access to
accomplish the same goal.

 

[Allen Browne]

Yes, forms are the interface for the user where you can do this sort of
thing. Queries are merely for retrieving the data, and do not have the
presentation power you are looking for.