background preloader


Facebook Twitter

Microsoft Access Flaws - Grouping by Memo field yields garbage. Flaws in Microsoft Access Provided by Allen Browne, July 2007, based on a posting by Peter Miller.

Microsoft Access Flaws - Grouping by Memo field yields garbage

This article describes a bug in Access 2000 and later (JET 4 and later), where a memo field is displays incorrect characters under these conditions: You GROUP BY a memo field. Your query contains a JOIN on an unindexed field. The display is typically one or two strange characters, instead of the contents of the memo field. You can download the demonstration database shown below (10kb, zipped, Access 2000 format mdb.) Demonstration TableOne consists of two fields: FieldA is a Number (Long Integer), with 3 records (the numbers 1, 2, and 3.)

TableTwo has one Number field (Long Integer), with 3 records (the numbers 1, 2, and 3.) The query joins the two tables, and groups by the fields in TableOne. Workaround Either of the following will work around the bug: Do not group by the Memo field. Other bugs There is a similar bug where concatenated text fields yield garbage, but applies to recordsets only. MS Access: Data Definition Query. Microsoft Access 97/2000 can utilize Data Definition SQL.

MS Access: Data Definition Query

The process is a bit convoluted but it does work. Access has the limitation that you can only create one table per Data-Definition Query. I recommend using an external text editor to inhibit MS Access' love for parenthesis and other odd characters. Then copy and paste your pure SQL code directly into the Access SQL Query window. My recommended text editor is the shareware product TextPad. Much of the following information was taken directly from MS Access' own help files. Use SQL data-definition queries to create, delete, or alter tables or create indexes in the current database. In the Database window, click Queries under Objects, and then click New on the Database window toolbar. Caution: If you convert a data-definition query to another type of query, such as a select query, you'll lose the SQL statement that you entered.

Create a hyperlink - Access - Microsoft Office Online. When you create a hyperlink (hyperlink: Colored and underlined text or a graphic that you click to go to a file, a location in a file, a Web page on the World Wide Web, or a Web page on an intranet.

Create a hyperlink - Access - Microsoft Office Online

Hyperlinks can also go to newsgroups and to Gopher, Telnet, and FTP sites.) by using any of the following methods, Microsoft Access creates a label control (label: A control that displays descriptive text, such as a title, a caption, or instructions, on a form or report. Labels may or may not be attached to another control.) on the form or report. Tip In Access 2010, in addition to adding hyperlinks, you can also add live web content to a form.

Read an article or try Office 2010! Although hyperlinks in a report won't work when viewed in Access, the hyperlinks will work when you output the report to Microsoft Word, to Microsoft Excel, or to HTML (HTML: The standard markup language used for documents on the World Wide Web. Notes. Sample Relational Database Data Models. Access Tips: Query and Filter Criteria. When constructing a query or a filter, you need to tell Access what to look for in each field.

Access Tips: Query and Filter Criteria

You do this by defining criteria - typing something (an "expression") into the Criteria cell of the query or filter grid. If you do not define any criteria for a particular field, Access assumes that you are applying no constraints and will display everything it has. This means that you only have to define criteria for those fields you are interested in. Here are some examples of the more common types of criteria. Often Access will complete the expression so that you need only type the text you want to match. The list of examples below is not exhaustive. This tutorial is arranged in the following sections: Matching Text When you enter text into the criteria cell your text should be enclosed in quotes ("") to distinguish it from other expressions and operators that you may need to add.

"Text" To match a word or phrase simply type the text you want to match. . ^ top Using Wildcards For example: Geekgirl's Plain-English computer tutorials.