Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hyperlink to text field conversion (access 2003)

    Is there any way to convert a hyperlink field to a text field without the resulting 3 part hyperlink format. I would like the resulting field to display only the address(e-mail) so that it can be importated into outlook contacts. Thanks

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

    Re: hyperlink to text field conversion (access 2003)

    Welcome to Woody's Lounge!

    The actual contents of a hyperlink field look like this:

    description#address#subaddress

    where the first and last parts may be empty. You will see this if you convert the hyperlink field to a text field or memo field (if you use a text field, make sure its field size is large enough). You can create an update query based on the table, add the former hyperlink field, and set the Update To line to:

    Mid([FieldName],InStr([FieldName],"#")+1,InStr(InStr([FieldName],"#")+1,[FieldName],"#")-InStr([FieldName],"#")-1)

    where FieldName is the name of the field.

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hyperlink to text field conversion (access 200

    This is partially successful. The resulting field has the desired address but is preceded by http:. When this field is imported into outlook contacts it is not a valid format. Can I now remove the http portion of the field.

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

    Re: hyperlink to text field conversion (access 200

    If you have already performed the first update query, you can now create another one, with the Update To: row set to
    <code>
    IIf(Left([FieldName],7)="http://",Mid([FieldName],8),[FieldName])
    </code>
    with the correct field name substituted.

    Note: make a copy of the table or of the database before trying this, just to make sure.

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hyperlink to text field conversion (access 200

    It worked perfectly. Thanks for the help

Posting Permissions

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