Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report listing hyperlinks (Access2003)

    A DB contains a hyperlink field which is meant for email addresses. The only way I've found to enable the end user to use this to open a mail client by clicking on it is to insist on them prefixing the email address with mailto: (is this correct?)
    The user wants a report listing each name and the associated email address, if it exists.
    Just using the email field in the report means that the report has lines like <pre>Joe Bloggs mailto:jbloggs@hotmail.com</pre>


    I'm trying to remove the mailto:
    I've set up a textbox which contains =IIf(IsNull([Email]),"",Right(Email.Value,Len([Email])-InStr(1,[Email],":"))) as the control source.
    This works OK if there is no data in the hyperlink field.
    If there is data, the mailto: is stripped off but a hash appears at the end of the text string, as in <pre> Joe Bloggs jbloggs@hotmail.com# </pre>

    Where has this # come from and how can it be removed, please?
    Thanks
    Silverback
    Silverback

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

    Re: Report listing hyperlinks (Access2003)

    The actual contents of a hyperlink field look like this:

    displaytext#address#subaddress

    where the displaytext and subaddress can be empty. When you look at a hyperlink field, Access will show the displaytext, or if that is empty, the address. It hides the actual contents. But functions such as Len and Right work with the actual contents.

    To get around this, use
    <code>
    =IIf(IsNull([Email]),"",Mid([Email],InStr([Email],":")+1,InStr(InStr([Email],":")+1,[Email],"#")-InStr([Email],":")-1))
    </code>
    I didn't create the expression like this! I started out defining separate calculated fields such as
    <code>
    P1: InStr([Email],":")
    </code>
    and
    <code>
    P2: InStr([P1]+1,[Email],"#")
    </code>
    and
    <code>
    Address: Mid([Email],[P1]+1,[P2]-[P1]-1)
    </code>
    When this tested out correctly, I substituted the expressions for P1 and P2.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> PS was <post#=677,762>post 677,762</post#> helpful?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report listing hyperlinks (Access2003)

    Thanks, again, Hans.
    Yes, post 677,762 was great. User has only just come back to me via email, hence late posting reporting back; she is very happy with the layout and sends you her thanks, too.
    Silverback
    Silverback

Posting Permissions

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