HELP: Issue when exporting a hyperlinked field to word or excel

[sam]

Hi All,

I am having an issue when I export fields that are hyperlinked in access to
export or to word. I get an extra string attached to the actual value,
something like "124 South West St#http://124 South West St#" and something
like this when i hyperlink it to a pdf doc "1234 street#C:\Documents\Appr
2027 Cullom Chicago, IL.pdf#"

Does anyone know how to resolve it?

Thanks in advance

 

[Arvin Meyer [MVP]]

That's because the standard hyperlink protocol is a web address.

You are better off not using the hyperlink datatype, which is prone to
failure for this and other reasons. Instead, simply save the data as a
simple text field data type. In a form, you can use the textbox hyperlink
property to jump to the place you want.

Application.FollowHyperlink Me.txtTextBox

or use a command button:

Me.cmdHyperlink.HyperlinkAddress = Me.txtTextBox

Now you won't have the export problem either.

 

[David W. Fenton]

You are better off not using the hyperlink datatype, which is
prone to failure for this and other reasons. Instead, simply save
the data as a simple text field data type. In a form, you can use
the textbox hyperlink property to jump to the place you want.

While I agree that it's good to avoid the Hyperlink datatype, a
255-character text field is not going to be large enough to hold
lots of URLs, so you'd need a memo field.

In reality, behind the scenes, the hyperlink field is actually a
memo field in the first place, but because it's flagged with a
property that makes it a hyperlink field, Access treats it
differently than it would plain memo field data. This is supposed
to be helpful, but, in fact, gets in the way.

 

[Arvin Meyer [MVP]]

While I agree that it's good to avoid the Hyperlink datatype, a
255-character text field is not going to be large enough to hold
lots of URLs, so you'd need a memo field.

Depending upon the URLs. The examples he used and anything similar would
most likely fit into less than 100 characters.

 

[David W. Fenton]

Depending upon the URLs. The examples he used and anything similar
would most likely fit into less than 100 characters.

"We will only ever need to store 2 phone numbers, so I think we can
just allocate two columns in the table."

"Our customers never have names longer than 25 characters, so I
don't think we need it to be any longer than that."

My philosophy is that if you can forecast it, you should accomodate
it. Assuming that the current scope of URLs being stored will never
change is, to say the least, unwise.

On the other hand, it's not all that hard to change a text field to
memo when the need arises, but what if your app is designed in a way
that silently truncates URLs beyond 255 characters? They may not
know they are losing data.

Of course, that's bad design, but I would suggest at the very least
that if you use a text field, that you test the length of the URL in
the BeforeUpdate event of the control you use to edit it and inform
the user if they attempt to insert anything longer than 255
characters. That would be the point at which you'd need to change
the field to memo.

Me, I'd just start out with memo fields for any URL field that's not
just going to be a top-level domain (like a customer website, which
is not likely to be anyting like this:

http://www.MyCustomerWebsiteWithAReallyLongName.com/page/customer.ent
ry.point.start.page/browse_topics/topic/dbf4077bd4fb9624/1d7d8b8566b2
e9ba?id=091f5b5a96efc949091f5b5a96efc94926e5cf92e5cbef8f84e09ca39ea78
ae026e5cf92e5cbef891f5b5a96efc94926e5cf92e5cbef8f84e09ca3

That's barely more than 255 characters, but you get the idea. Sure,
it's unlikely for some types of URLs you'll store, but should you be
storing URLs of web database searches, you really will need to plan
for URLs like that.

 

[Arvin Meyer [MVP]]

I understand what you are saying, but considering that a 128 character GUID
string yields:

7.237005577332262213973186563043e+75

number of combinations, I'm not going to worry about the URL that might
exceed the 255 character string length. I've seen some pretty long URLs, but
none anywhere near the length that would break it. That said, I agree that
it is the responsibility of database architects to know of design problems
and make the owner aware of them. Your phone example is one where I usually,
but not always build a table for additional communication numbers and yet
another to tell me what type it is (phone, fax, cell, etc.), but not always.
If the owner wants only 2 phone numbers, and he is made aware of the
possible pitfalls, he gets 2 phone fields. Schemas can be changed as new
requirements become known, and while I agree, that it is usually better to
do it in advance from a design standpoint, the exigencies of actually
building the database come first.