Management Strategies Inc 

  An International Company

How to add calculated fields to queries
Some times you may want to display the results of a calculation in a query. For example you have a query that displays invoice details, including the number of items of each type bought and the unit price of the items. To display the total spent on each item you can add a calculated field to the query. You create a calculated field by entering an expression into an empty field cell in the query design grid.

       For example: Items_Total: [Quantity]*[Unit_Cost]

In a calcuated field you enter the name you wish to give the field first followed by a colon ':'. Then enter the expression for the calculation, in this case the quantity field multiplied by the unit cost field. You can perform all sorts of calculations in a query using calculated fields. You can even add criteria to calculated fields.

For example, in the above scenerio you could limit the query output to records whose Items_Total was greater than 500 by adding '>500' to the criteria grid under the [Items Total] field.

How can I prevent Users from adding records to my forms?
If you can't get hold of better behaved users then you will need to use the Default Editing property of the Form.  Firstly open your form in Design View and display the property sheet (VIEW, PROPERTIES). In the Default Editing property set it to Can't Add Records. Save the Form and go back to Form View to make sure it works.

How to print multiple columns in a report
Firstly, you need to work out how many columns you need and whether they will fit onto the page. Then open the report in Design View and select FILE, PAGE SETUP, COLUMNS. For Grid Settings, enter the number of columns you want, the amount of space between rows and in the Column Spacing text box enter the amount of space you need between columns. For Column Size enter the column Width and then the Height of the detail section of the report. In Column Layout you can choose Down, Then Across which will produce horizontal columns, or Across, Then Down which will produce a layout similar to mailing labels. Then select PAGE, and under Orientation choose either portrait or landscape. Click OK. 

Consecutive numbering for each record in a Report
Add an unbound text box to the relevant section of the report. In the properties for the text box change the ControlSource property to =1. Then change the Running Sum property.   If your records are grouped and you want to number each group separately then change the Running Sum property to Over Group. Otherwise change it to Over All. 

Can I convert my Form into a Report?
If you have a form that you wish to save as a report open that form in Design View. Then select FILE and SAVE AS REPORT. 

How to load your Report into MS Word
Open up your report, or from the Database window, click the name of the report you want to save and load as a Word document.     Select TOOLS, OFFICE LINKS, and click Publish It With MS Word. The output is saved as a Rich Text Format (.rtf) file in the folder where Microsoft Access is installed. Word will automatically start up and opens the document. You can then treat the document like any other Word document.   You can also follow this method to load the output of a datasheet or form into Microsoft Word, or just a selection of a datasheet.

 Using Shortcut Keys

Do you find that using the keyboard is sometimes quicker than using your mouse? Shortcut keys can help you bypass menus and carry out commands directly. You can use shortcut keys in many ways with Access, from accessing commands and toolbar buttons to inserting today's date. Shortcut keys are sometimes listed next to the command name on Access menus. For example, on the File menu, the Print command lists the shortcut CTRL+P.

For a comprehensive list of shortcuts, ask the Office Assistant for help. In Access 2000 or any of the other Office 2000 applications, press F1 to display the Assistant, and then type shortcut keys in the text box. Here are some of the most useful Access shortcut keys:

Joining Two Strings of Text Together

You can join two strings of text together by using the & operator in an expression. This is called concatenation. For example, the result of both of the following expressions is the text string Hello world!

"Hello " & "world!"                    "Hello" & " " & "world!"

You can use concatenation to combine the text in two or more fields into a single text string, and you can display the result in a field in a query, or in a control on a form or report.

For example, if you have a table that contains the fields FirstName and LastName, you can use the following expression to create a text string that displays the values of the FirstName and LastName fields separated by a space.       [FirstName] & " " & [LastName]

To see how you can use this expression in a form, open the Northwind sample database, and then open the Employees form in Design view.   The & operator appears in the text box form header.  In a calculated control on a form or report, precede the expression with the = operator. Switch to Form view, scroll records, see the results.

Another example - open the Customer Labels report in Design view. The following expression in the Address2Line box uses & operator to combine values - City, Region, and PostalCode - into a single string text.   =Trim([City] & " " & [Region] & " " & [PostalCode])

In this expression, the Trim function removes any leading or trailing spaces. For a more information about concatenation, type combine text values from multiple fields in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

Using String Manipulation Functions

You can use functions in your expressions to count characters, return characters, and determine the location of one string within another. By using the functions together, you can manipulate text strings in countless ways. As an example of the flexibility you gain by using functions in expressions, this article shows you how to separate a text string into two strings.

The string manipulation functions that you can use in Access include Len, Left, Right, and InStr.

The Len Function

Len(string) returns the number of characters in the string. You can specify the string itself in quotation marks, or you can specify the field that contains the text string.

The Left Function   Left(string, length) returns a specified number of characters from the left side of a string. The first argument, string, specifies the text string, or the field containing the string, that you want to extract the result from. The second argument, length, specifies the number of characters that you want to return.

The Right Function  Right(string, length) returns a specified number of characters from the right side of a string. String specifies the text string, or the field containing the string, that you want to extract the result from; length specifies the number of characters that you want to return.

The InStr Function

InStr([start, ]string1, string2[, compare]) returns a number specifying the position of the first occurrence of one string within another.

• The first argument, start, is optional, and it is a number that specifies the position of the character where you want to begin searching. For example, if start is 3, the search begins at the third letter in the string. If you omit this argument, the search begins at the first character in the string.
• The second argument, string1, is the string, or the field containing the string, that you are searching in.
• The third argument, string2, is the string, or the field containing the string, that you are searching for.
• The fourth argument, compare, is optional, and it determines how the InStr function compares string1 and string2. The default comparison type is binary and therefore case-sensitive.

Separating a String of Text into Two Strings: Step One

You can use string manipulation functions in expressions to separate a text string that contains a space into two strings. For example, in a table, you might have a FullName field that contains the first name and the last name separated by a space. You can open the table in Design view and add the fields FirstName and LastName. Then you can use an update query to parse the data in the FullName field and populate the new fields with the correct data.

The first step is to extract the first name - the text to the left of the space. If you know the position of the space character, you can use that number with the Left function to extract the first name. In a record where the field FullName contains the text string Robert King, InStr(1, [FullName], " ") returns 7, the position of the space character. However, the expression Left("Robert King", 7) returns the first name, Robert, as well the space character, which you don't need. The number of characters you actually need is one less than the number returned by InStr(1, [FullName], " "). Therefore, the expression that returns the first name is:

Left([FullName], InStr(1, [FullName]," ") - 1)   (You can use this expression in an update query.)

Separating a String of Text into Two Strings: Step Two

The next step is to extract the last name - the text to the right of the space character. The total number of characters in FullName is the number of characters to the left of the space plus 1 (for the space itself) plus the number of characters to the right of the space. If the expression InStr(1, [FullName], " ") returns the number of characters to the left of and including the space, then the number of characters to the right of the space is Len([FullName]) - InStr(1, [FullName], " "). Therefore, the expression that returns the last name is:

Right([FullName], Len([FullName]) - InStr(1, [FullName], " ")) (You can use this expression in an update query.)

For more information about update queries, type change records as a group by using an update query in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search. 

Sample expressions that you can use to extract a portion of a text string:

 These expressions are commonly used in the Update To line of an update query to place a portion of a larger field into a new field. You can adapt these expressions so that you can use them with other common formats. For example, you can use the expression that is used to extract "Doe" from "Doe, John" to extract "Seattle" from "Seattle, WA."

MORE INFORMATION

The following expressions are sample expressions that you can use to extract a portion of a text string.
NOTE: In the following sample expressions, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating the expression.

First Name

Original Entry in [Names]: "John Doe"
Returned by Expression: John
Expression: Expr: Left([Names],InStr(1,[Names]," ")-1)

First Name

Orignial Entry in [Names]: "Doe, John"
Returned by Expression: John
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1, _
[Names]," "))

First Name

Original Entry in [Names]: "John P. Doe"
Returned by Expression: John
Expression: Expr: Left([Names],InStr(1,[Names]," ")-1)

Last Name

Original Entry in [Names]: = "John Doe"
Returned by Expression: Doe
Expression:Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(1, _
[Names]," "))

Last Name

Original Entry in [Names]: "Doe, John"
Returned by Expression: Doe
Expression: Expr: Left([Names],InStr(1,[Names],",")-1)

Last Name

Original Entry in [Names]: "John P. Doe"
Returned by Expression: Doe
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr(InStr _
(1, [Names]," ")+1,[Names]," "))

Last Name

Original Entry in [Names]: "John Doe" or "John P. Doe"
Returned by Expression: Doe
Expression: Expr: IIf(InStr(InStr([names]," ")+1,[names]," ") _
<>0, Right([names],Len([names])-InStr(InStr([names]," ")+1, _
[names]," ")),Right([names],Len([names])-InStr([names]," ")))

Middle Initial

Original Entry in [Names]: "John P. Doe"
Returned by Expression: P.
Expression Expr: Trim(Mid([Names],InStr(1,[Names]," ")+1,InStr(InStr _
(1, [Names], " ")+1,[Names]," ")-InStr(1,[Names]," ")))

Middle Initial

Original Entry in [Names]: "Doe, John P."
Returned by Expression: P.
Expression: Expr: Right(Trim([Names]),Len(Trim([Names]))-InStr _
(InStr(1, [Names]," ")+1,[Names]," "))

Zip Code

Original Entry in [ZipCode]: "98052-6399"
Returned by Expression: 98052
Expression: Expr: Left([ZipCode],5)

Area Code

Original Entry in [Phone]: (425) 635-7050
Returned by Expression: 425
Expression: Expr: Mid([Phone], 2, 3)

Phone Number

Original Entry in [Phone]: (425) 635-7050
Returned by Expression: 635-7050
Expression: Expr: Right(Trim([Phone]),8)

Referential Integrity

What is referential integrity?

Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data. You can set referential integrity when all of the following conditions are met:

• The matching field from the PRIMARY TABLE is a PRIMARY KEY or has a UNIQUE INDEX.
• The related fields have the same data type. There are two exceptions. An AutoNumber field can be related to a Number field with a FieldSize property setting of Long Integer, and an AutoNumber field with a FieldSize property setting of Replication ID can be related to a Number field with a FieldSize property setting of Replication ID.
• Both tables belong to the same Microsoft Access database. If the tables are LINKED TABLES, they must be tables in Microsoft Access format, and you must open the database in which they are stored to set referential integrity. Referential integrity can't be enforced for linked tables from databases in other formats.

When referential integrity is enforced, you must observe the following rules:

• You can't enter a value in the FOREIGN KEY field of the related table that doesn't exist in the primary key of the primary table. However, you can enter a NULL value in the foreign key, specifying that the records are unrelated. For example, you can't have an order that is assigned to a customer that doesn't exist, but you can have an order that is assigned to no one by entering a Null value in the CustomerID field.
• You can't delete a record from a primary table if matching records exist in a related table. For example, you can't delete an employee record from the Employees table if there are orders assigned to the employee in the Orders table.
• You can't change a primary key value in the primary table, if that record has related records. For example, you can't change an employee's ID in the Employees table if there are orders assigned to that employee in the Orders table.

If you want Microsoft Access to enforce these rules for a relationship, select the Enforce Referential Integrity check box when you create the relationship. If referential integrity is enforced and you break one of the rules with related tables, Microsoft Access displays a message and doesn't allow the change.

You can override the restrictions against deleting or changing related records and still preserve referential integrity by setting the Cascade Update Related Fields and Cascade Delete Related Records check boxes. When the Cascade Update Related Fields check box is set, changing a primary key value in the primary table automatically updates the matching value in all related records. When the Cascade Delete Related Records check box is set, deleting a record in the primary table deletes any related records in the related table.

Return to Tips