Merging hyperlink/web fields

[John]

I am trying to merge a list of web sites stored in a an access table onto a
word doc. The web addresses are stored in the access table in a field with a
datatype 'Hyperlink'.

When info is merged it comes out like this:

www.drteethbigband.co.uk#http://www.drteethbigband.co.uk#

Any help to stop this happening greatly appreciated.

 

[Peter Jamieson]

There are at least two different problems here:
a. Word doesn't recognise the datatype "Hyperlink" (i.e. does not treat the
contents as a hyperlink). It just treats the content of the field as text.
b. if you are trying to use a Hyperlink field to generate a proper
hyperlink in Word, the usual problem is that when you merge, the /display
text/ of the field remains the same even when the data is different in every
record. This is simply because there is no way to specify the display text
in the field code - you can only do it programmatically, e.g. via VBA.

As far as extracting the hyperlink from Access is concerned, you can do it
in an Access query and use that query as the data source for your merge. As
far as I can tell, hyperlinks are basically stored as

displaytext#linktext

e.g. if the hyperlink column is called h, your query SQL could be something
like

SELECT Mid(h, instr(1, h, '#') + 1, Len(h) - instr(1,h,'#') - 1) as
hyperurl, * FROM mytable

then you can insert the link using a nested field, e.g.

{ HYPERLINK "{ MERGEFIELD hyperurl }" }

or it may be better to insert the linktext directly using

{ MERGEFIELD hyperurl }

and find some way to get Word to convert the text to proper hyperlinks
(probably by mucking around with the Autoformat options).

Peter Jamieson