Formatting Hyperlinks in an Access 2007 Database

In the first article in this series of four, we discussed the different types of hyperlinks you can store in an Access 2007 data table. It turns out that Access 2007 can understand File, FTP, HTTP, Mailto, and News hyperlinks. The second article in this series explored how to edit the data field type in an Access table to accept hyperlinks. This article will show you how to properly form hyperlinks in an Access 2007 data table to include the display text, Web address, sub-address, and screen tip.

Forming hyperlinks in an Access 2007 database is different from entering hyperlinks into other Microsoft Office 2007 applications. Word, for example, assumes that you want to include a hyperlink in a document whenever you type http:// or when a string of text follows this pattern:

www.somedomain.com

In fact, in Word 2007, this assumption forces you to specifically tell Word that you don’t want such a string of text to be a hyperlink. Forming hyperlinks in Access takes a more planning but the features available in Access are far more powerful than in other applications. Hyperlinks in an Access 2007 database are made up of four parts. One part is mandatory while the remaining three are optional.

The only required component in an Access 2007 database hyperlink is the Web address itself. This makes sense because without the Web address there is no reason to format text to be a hyperlink. The hyperlink itself is actually just text that has been formatted to be a hyperlink (see the previous article in this series for more information). However, the remaining three optional components make hyperlinks in an Access 2007 database so powerful.

The first optional component to an Access hyperlink is the display text. If you have any experience using the Internet, you know that the text that you click on to go to another web page need not display the destination of the link in the hyperlink text. This gives you the option to display your Access hyperlinks in a more organized and information-rich fashion.

The second optional component is the sub-address where you can specify a link or location within a web page or document. This means that you can specify to which bookmark or other area of a document you wish to link to from your Access database.

The third and final optional component in an Access hyperlink is the screen tip. The screen tip is more information that you can provide a user of the database when the user hovers his/her mouse over the hyperlink. This is a common practice on web page so that viewers can see more information without the web page author having to clutter up the page with explanations of every link. This powerful feature allows you keep your database free from complexity.

Formatting hyperlinks in an Access 2007 database is more complex than adding links in other Microsoft Office applications. Remember that there is only one mandatory component to adding a hyperlink to a data table’s cell; the Web address is the only mandatory component. To add only a hyperlink to a data table’s cell, simply type in the link using the following format:

www.somedomain.com

Access will then display the following hyperlink in the cell as:

http:// www.somedomain.com

To add display text as discussed above, you need to use a pound (#) symbol to separate the display text from the hyperlink text. To add the display text “Some Domain” to the hyperlink, format the link like this:

Some Domain# http:// www.somedomain.com#

Notice that there is an extra # symbol at the end of the hyperlink. This is because the pound symbol is used to separate all of the options in an Access 2007 hyperlink and you need to tell Access where one option ends and another begins.

To add a sub-address to the hyperlink, format the link like this:

Some Domain# http:// www.somedomain.com#somesubaddress#

Notice again that the hyperlink ends in a # symbol. This is used to tell Access that the hyperlink has ended and includes everything we want it to include. To include all four options in the hyperlink, format the link like this:

Some Domain# http:// www.somedomain.com#somesubaddress#This is a screen tip#

Therefore, the general format for a hyperlink in Access 2007 is:

displaytext#webaddress#sub-address#screen tip

If you want to skip one of the options, you need to use a double pound to tell Access that the option in question is purposely missing. For example, suppose you want to have a hyperlink that has some display text (optional), an address (mandatory), no sub-address (optional), and a screen tip. You would format that hyperlink like this:

Some Domain# http:// www.somedomain.com##This is a screen tip#

Notice the double # symbol between the address and the screen tip. This double pound is used as a placeholder to show that the sub-address was intentionally left blank.

Formatting hyperlinks in an Access 2007 database takes some planning, However, the result is a much more robust and feature-filled link in comparison to links in other Microsoft Office 2007 applications. Once you format a data field to accept hyperlinks (see article 2 in this series), you can use the formatting options in this article to create information-rich hyperlinks that tell more of a story than just using the web address as the hyperlink.