ACC2000: HTML: Search for Hyperlink Fails with Specified Criteria (207806)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q207806
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you enter a hyperlink address in the Criteria cell of a query to return all the records that are associated with that hyperlink, no records are returned. This happens even though records in the table appear to meet the criteria that you specified.

CAUSE

The criteria does not contain the full, underlying value of a Hyperlink field.

RESOLUTION

When you search for a hyperlink address, include the asterisk character (*) within your search criteria. For example, to retrieve all records that contain www.microsoft.com, type: LIKE "*www.microsoft.com*"

MORE INFORMATION

Microsoft Access Hyperlink fields have the following three sections. The sections are separated by number signs (#):
  • Text to display - the text that appears in a field or control.
  • Address - the path to a file (UNC path) or page (URL).
  • Subaddress - a location within the file or page.
For example

Microsoft Corporation#http://www.microsoft.com#Sample

where:

Text to display = Microsoft Corporation
Address = http://www.microsoft.com
Subaddress = Sample

When you view Hyperlink fields in a datasheet, in a form, or in a report, Microsoft Access shows only the display text portion of the fields. Or if you entered a UNC path or a URL without display text, Access shows only the path or the URL. Because the Datasheet (or another) view may not display the entire value associated with a Hyperlink value, place an asterisk (*) on either side of your criteria.

Steps to Reproduce Behavior

  1. Open the sample database Northwind.mdb.
  2. Open the Suppliers table, and then scroll to the HomePage field. Note the names of the first two Home pages:

    CAJUN.HTM
    Mayumi's (on the World Wide Web)

  3. Close the Suppliers table. Create a new query based on the Suppliers table, and add the HomePage field to the query grid.
  4. Type the following criteria in the Criteria cell:

    "CAJUN.HTM" or "Mayumi's (on the World Wide Web)"

  5. Run the query. Note that the query returns no records.
  6. Switch back to Design view of the query, and type the following criteria in the Criteria cell:

    "*CAJUN.HTM*" or "*Mayumi's (on the World Wide Web)*"

  7. Run the query again. Note the query now returns the correct records.NOTE: To view the entire Hyperlink value:

    1. Add the following expression to the query design grid:

      Expr1: Mid([HomePage],1)

    2. Run the query.
You can now see the entire contents of the field, including the number signs (#). This can help you identify what criteria will work best for you in your searches.

REFERENCES

For more information about Hyperlink fields, click Microsoft Access Help on the Help menu, type hyperlink fields and controls in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbprb kbusage KB207806
©2004 Microsoft Corporation. All rights reserved.