UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> ParseHyperlinkField    

Access stores hyperlink field values differently than others. The field value is stored with the hyperlink address inside a set of hash (#) marks, and an optional display text preceeding that. (example: www.utteraccess.com#http://www.utteraccess.com#). This function can be used to get either the text or hyperlink part of the field's value.

Be sure to include the Enum in the Declarations section of any standard module!

Public Enum FieldHyperlinkValue
 FieldHyperlinkText = 0
 FieldHyperlinkAddress = 1
End Enum

'   Code courtesy of UtterAccess Wiki
'   http://www.utteraccess.com/wiki/index.php/ParseHyperlinkField
'   Licensed under Creative Commons License
'   http://creativecommons.org/licenses/by-sa/3.0/
'   You are free to use this code in any application,
'   provided this notice is left unchanged.
' NAME: ParseHyperlinkField
' DATE: 11/26/2012
' Parses an Access Hyperlink field to return either the hyperlink text or address
' Accepts variant input for null handling
' Returns Variant (string) for null handling
' If the "text" part doesn't exist, the hyperlink address is returned
' Returns ZLS on ZLS input
' Returns Null on Null input
' Access stores the hyperlink field like so:
'    HyperlinkTextGoesHere#HyperlinkAddressGoesHere#
' If the text is input in as fully qualified address, the "text" portion is omitted, thus:
'    #HyperlinkAddressGoesHere#
' Generally, it appears that Access will prefix the "http://" protocol to the entered value.
' If a different protocol other than http:// is used, it will not prefix.  Examples:
'    #ftp://someaddress.ext#
'    #https://someaddress.ext#
' Local and Server file protocalls are recognized and are not prefixed, although it appears that
' server shares are given both a text and address part:
'   #C:\testme.txt#
'   #file:///c:\testme.txt#
'   //server/share/folder/file.txt#//server/share/folder/file.txt#
Public Function ParseHyperlinkField(hLink As Variant, Optional ValueToGet As FieldHyperlinkValue = FieldHyperlinkText) As Variant
On Error GoTo Err_Proc

 Dim Ret As Variant
 Dim hl As String  'coerce string instead of variant for working in the procedure

 'return null if input is null
 If IsNull(hLink) Then
   Ret = Null
   GoTo Exit_Proc
 End If
 'force to string and trim it
 hl = Trim(CStr(hLink))
 'return NLS if input is zls
 If Len(hl) = 0 Then
   Ret = ""
   GoTo Exit_Proc
 End If
 'check if the string starts with a hash, if so there's no text part, return the hyperlink part
 If Left(hl, 1) = "#" Then
   hl = Mid(hl, 2, Len(hl) - 2)
   Ret = hl
   GoTo Exit_Proc
 End If
 'if we made it this far there's both a text and hyperlink part
 'find out which the caller wants and return it
 If ValueToGet = FieldHyperlinkText Then
   'get the text part
   hl = Left(hl, InStr(1, hl, "#") - 1)
   Ret = hl
   'get the address part
   hl = Mid(hl, InStr(1, hl, "#") + 1)
   hl = Left(hl, Len(hl) - 1)
   Ret = hl
 End If

 ParseHyperlinkField = Ret
 Exit Function
 Select Case Err.Number
   Case Else
     MsgBox "Error!" & vbCrLf & Err.Number & ": " & Err.Description _
         & vbCrLf & vbCrLf & "Module4.ParseHyperlinkField"
 End Select
 Resume Exit_Proc
End Function

Creative Commons License
ParseHyperlinkField by UtterAccess Wiki is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Editing and revision of the content is freely encouraged; for details, see Expected Usage.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 2,470 times.  This page was last modified 12:33, 26 November 2012 by Jack Leach.   Disclaimers