Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail merge an access database hyperlink field (Access 2000 SR-1)

    Hi guy's, i have emailed Woddy's access watch with this question but wondered if anyone else could help.
    I am working on an access 2000 database at work which is undergoing redevelopment. I have been asked to provide the facility to hyperlink to related documents in our document management facility, which I have done using the hyperlink field. It is possible to search the database and display the results in a continuous form, which includes the hyperlinked field. The form has the ability to be merged into word which works fine, however, I have been asked if it is possible to merge the hyperlink field into the document, as a working hyperlink link. I can not get this to work and have played around for a couple of days.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge an access database hyperlink field (Access 2000 SR-1)

    The problem is that a hyperlink field in Access is stored as a string of the form "DisplayText#HyperlinkAddress#SubAddress" in which the first and third part can be empty. So in general, the "real" contents of a hyperlink to a document will be something like "#C:WordTest.doc#". If you include this in a hyperlink in Word, it won't be interpreted correctly.

    The easiest way out would be to store the paths to the documents in an ordinary text field. In Access, you can use Application.FollowHyperlink to activate the links, and in Word, you could insert a MergeField nested inside a Hyperlink field. It would look like this with field codes displayed:

    { HYPERLINK "{ MERGEFIELD Hyp }" }

    Note 1: Replace Hyp by the name of the hyperlink field.
    Note 2: You can toggle field codes on/off by pressing Alt+F9.
    Note 3: You must NOT type the field brackets { } yourself; insert them by pressing Ctrl+F9.

    If you prefer to keep the hyperlink field in Access, create a query based on the table containing the hyperlink field, and include a calculated field in the query that extracts the path. The following horrible formula will do this:

    Hyp2: Mid([Hyp],InStr([Hyp],"#")+1,InStr(InStr([Hyp],"#")+1,[Hyp],"#")-InStr([Hyp],"#")-1)

    where Hyp is the name of the hyperlink field. Use the query instead of the table as data source for the mail merge, and use Hyp2 instead of Hyp in the nested field code given above.

  3. #3
    New Lounger
    Join Date
    May 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge an access database hyperlink field (Access 2000 SR-1)

    Thanks Hans, i have tried both of theses methods but could not get it to work. I will continue to try these, possible concentrating on the nested field example. Thank you for your help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge an access database hyperlink field (Access 2000 SR-1)

    If you still have problems, you can post back with details of what you tried and where it fails.

  5. #5
    New Lounger
    Join Date
    May 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge an access database hyperlink field (Access 2000 SR-1)

    Hans thank you for your help and i have now got option 2 working but is it possible to get the hyperlink displayed text to read something different. ie document name only, other than the full path?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge an access database hyperlink field (Access 2000 SR-1)

    I'm sorry, I don't know how to do this from a mail merge. The text to display for a hyperlink is not part of the field code, AFAIK it can only be set interactively or in VBA. Let's hope that somebody else will prove me wrong.

  7. #7
    New Lounger
    Join Date
    May 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge an access database hyperlink field (Access 2000 SR-1)

    Thank you for your help.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Mail merge an access database hyperlink field (Access 2000 SR-1)

    Would it be possible to store the hyperlink address as a simple text string, but display it as a hyperlink on the form? Then you could easily merge it to a hyperlink field in Word. We used that technique in an automated emailing to several thousand individuals where we wanted each person to have a unique website address to go to.

    Another alternative would be to use Automation to create your Word document, rather that doing it with mail merge. That gives you much greater control over how things are formatted in Word, and gets around some of the problems associated with mail merges in general.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •