Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 197373 - Last Review: July 14, 2004 - Revision: 1.1
ACC2000: You Cannot Follow Hyperlinks in Totals Query
This article was previously published under Q197373
Novice: Requires knowledge of the user interface on single-user computers.
When you create a totals query that uses the SQL aggregate (totals)
functions
First() or
Last() on a Hyperlink field, the query results do not display a usable Hyperlink field. Instead, you see the Displaytext, Address, and Subaddress portions of the hyperlink displayed as text.
If you want to use a Hyperlink field in a totals query, append the query
results to an existing table that contains a Hyperlink field. Then you can
open the table directly or create a select query based on the table to view
the results, and you'll be able to follow the hyperlinks.
The following example uses the Suppliers table in the sample database
Northwind.mdb to illustrate how to do this:
- Open the sample database Northwind.mdb.
- Create the following table named Suppliers2 in Design view:
Table: Suppliers2
-----------------------
Field Name: Region
Data Type: Text
Field Name: HomePage
Data Type: Hyperlink
- Save the Suppliers2 table and close it. You do not need to create a
primary key.
- Create the following new query in Design view based on the Suppliers
table:
Query: HomePageQuery
--------------------
Type: Select Query
Field: Region
Table: Suppliers
Field: HomePage
Table: Suppliers
- On the View menu, click Totals to create a totals query.
- In the QBE grid, select Group By in the Total row for the Region column; select First in the Total row for the HomePage column.
- On the Query menu, click Run. Note that the FirstOfHomePage column does not contain usable hyperlinks.
- On the View menu, click Design View.
- On the Query menu, click Append Query.
- In the Append dialog box, select Suppliers2 in the Table Name box, and then click OK.
- In the QBE grid, select Region in the Append To row of the Region column; select HomePage in the Append To row of the HomePage column.
- On the Query menu, click Run. Click Yes when prompted if you want to append the selected rows.
- Close the query without saving it, and open the Suppliers2 table in
Datasheet view. Note that the table contains the results of your
query, including usable hyperlinks.
When you create a totals query that uses the
First() or
Last() function on a Hyperlink field, Microsoft Access displays the text equivalent of the Hyperlink field. The text of the field consists of three sections separated by number signs (#): the Displaytext, the Address, and the Subaddress.
For more information about the different parts of a Hyperlink field, click
Microsoft Access Help on the
Help menu, type
datatype property in the Office Assistant or the Answer Wizard, and then click
Search to view the topic.
APPLIES TO
- Microsoft Access 2000 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate