Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hyperlink in access - transfering to word format (Access 2002/Word 2002)

    In doing a mail merge from access the hyperling in the mergefield appears as www.xyz.com#http:www.xyz.com#. I want the mergefield to only show www.xyz.com.. Any suggections?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: hyperlink in access - transfering to word format (Access 2002/Word 20

    Is it supposed to be an active hyperlink in Word? Formatting would need to be done in the Word field.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hyperlink in access - transfering to word format (Access 2002/Word 20

    No it not a hyperlink. It is coming through as a field only and does not show as a hyperlink. I use this to confirm client information to them in a printable format. it is strictly a {mergefield} in Word.

    See attached example
    Attached Files Attached Files

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: hyperlink in access - transfering to word format (Access 2002/Word 20

    If using Access hyperlink field, the "#" symbol is used as delimiter for hyperlink. If you want to export hyperlink field to Word (or other format) w/o the delimiters, you can use Replace function in query expression to get rid of the "#" symbols. Example:

    SELECT Table1.Link, Replace(Nz(<!t>[Link]<!/t>,""),"#","") AS [Link Text]
    FROM Table1;

    In example "Link" is name of hyperlink field in Table1. The Nz function is used to avoid errors if Link field is blank (null). Note that Replace function may not work in query expression in earlier versions of Access; you'd have to wrap Replace in a user-defined function which could then be used in query expression. The Replace function was introduced in VB/VBA 6.0 and therefore cannot be used at all in Access 97.

    HTH

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: hyperlink in access - transfering to word format (Access 2002/Word 20

    In further reply, I realized a hyperlink field may include an optional descriptive name for link, which precedes actual address; and, following address, an optional subaddress and optional screentip text. Each of these elements is delimited by the number/pound/hash (#) symbol. So if you want to export address part only, previous suggestion will not work. Instead, you can use a user-defined function like this example to parse hyperlink address only from hyperlink field:

    Public Function GetHyperlinkAddress(ByVal strLink As String) As String

    On Error Resume Next

    Dim intStart As Integer
    Dim intEnd As Integer

    intStart = InStr(1, strLink, "#") + 1
    intEnd = InStr(intStart, strLink, "#")

    GetHyperlinkAddress = Mid(strLink, intStart, intEnd - intStart)

    End Function

    Example: Suppose I have a hyperlink field defined as:

    <font color=blue>amazon dot com#http://www.amazon.com#subaddress#screentip text#</font color=blue>

    In normal view, this field will be displayed as:

    <font color=blue>amazon dot com</font color=blue>

    Note: When editing hyperlink field, press F2 key to see actual text with delimiters. In this example, the Replace function shown in previous reply will return:

    amazon dot comhttp://www.amazon.comsubaddressscreentip text

    Which is not desired result. The GetHyperlinkAddress function will return:

    http://www.amazon.com

    Which would be desired result. Example of use in a query (various functions used for comparison purposes):

    SELECT Table1.Link, CStr(Nz(<!t>[Link]<!/t>,"")) AS <!t>[Link Text Delim]<!/t>, Replace(Nz(<!t>[Link]<!/t>,""),"#","") AS <!t>[Link Text No Delim]<!/t>, GetHyperlinkAddress(Nz(<!t>[Link]<!/t>,"")) AS <!t>[Link Address]<!/t>
    FROM Table1

    The second expression above uses CStr function to display actual text, including delimiters, contained by hyperlink field. The GetHyperlinkAddress function should return correct results, assuming hyperlink info entered correctly in first place.

    HTH

Posting Permissions

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