Access Hyperlink format fatally flawed?

[Jon Furman]

Anyone else use a field formatted as a hyperlink to store URLs? I was just
doing this in an Access db I was developing when I ran into a big problem.
The case where the URL contains a #. Since the hyperlink data type is really
a string it uses the # as a delimiter to seperate the information items in
the hyperlink datatype. The help article called "About Hyperlinks",
subsection "Examples of hyperlink addresses" explains this. Unlike most
situations where this is a problem there doesn't appear to be an escape
sequence or convention for embedding the # char. I thought that I would be
clever and use the HTML code for # but quickly to my horror discovered that
this code is: #. So that's not going to work. Could the hyperlink type
really be so flawed? Hopefully I'm missing something, and won't have to
store the elements of the hyperlink type in seperate string fields. Thanks
to anyone who can shedsome light on this.

Jon

 

[david]

Encode # like this: %23

Use Windows Calculator in Scientific Mode (View/Scientific) to
convert number from decimal to hex if you don't know the hex
value to use in URLencode. :~)


Apart from that, is there a problem?

What kind of a URL are you using? # used to indicate where a
fragment identifier (bookmarks/anchors in HTML) begins. That
is how it is used in an Access I think?

http : // url_first_part # url_second_part

By extension, in Access you also get a preceding part, but that
is consistant with the system:

my_hyperlink_zero_part # http : // url_first_part # url_second_part

(david)

 

[Jon Furman]

Thanks for your reply David. I had forgotten about URL encoding, but
unfortunatley that doesn't solve it completely. Consider a URL pointing to a
file called csharp.htm located on the desktop, the access format for the url
would be:


csharp.htm#C:\Documents and Settings\User\Desktop\csharp.htm##csharp.htm

So that would display csharp.htm in a form, open the desktop file when
clicked and have the pop up tip display csharp.htm. All good so far. Now
replace the sharp with # and watch all heck break loose:

c#.htm#C:\Documents and Settings\User\Desktop\c#.htm##c#.htm

Access displays "c", thinks ".htm" is the address and somehow gets .htm for
the tip.

Now I could replace the third # with a %23 just to try to get the URL part
working, but that won't work for local URLs.

It's looking bleak for the hyperlink data type.....

 

[Allen Browne]

Yes: the hyperlink type is flawed.

IMHO, it is better to use a Text (or possibly Memo) field to store this
data, and then figure out how to follow the link with some code that is a
bit more intelligent.

Here's an introduction to some of the issue like the one you experienced:
Hyperlinks: warnings, special characters, errors
at:
http://allenbrowne.com/func-GoHyperlink.html

 

[Jon Furman]

Thanks for confirming the disfunction of the hyperlink data type. I'm
surprised that it's flawed on such a fundamental level. I'll look at your
code, thank you for sharing that. I think that for my purposes I'll just
need to store the elements of the hyperlink in seperate text fields.


Jon

 

[david]

C:\Documents and Settings\User\Desktop\c#.htm

Works in Internet Explorer, Firefox and Opera automatically
urlencode it as
file:///C:/Documents%20and%20Settings/david/Desktop/c%23.htm
and
file://localhost/C:/Documents%20and%20Settings/david/Desktop/c%23.htm
before opening... Useless for you, but I guess I wouldn't say that
Hyperlink format is fatally flawed because it fails on illegal URLs.
Glad Allen had a solution for you.

(david)