Le Moyne College CSC-151 Microsoft Access 2003 Reference

Datasheet view advanced features

The toolbar in the Datasheet view contains many features which are useful.

The following list explains each datasheet feature found on this toolbar.

	Switch to design view	Clicking on this button takes you from datasheet view to design view. Clicking on the drop down arrow on this button displays a menu showing all of the views supported by the Access object being viewed. You may select any view from the menu.
	Save	Save any changes made to the design of this datasheet view. For example, changing the width of a column, adding or changing a filter, or sorting the data are all design charactersistics of the datasheet view. These types of changes need to be saved. However, it is not necessary to save any changes to the data displayed in this view - these changes are automatically saved to the database.
	File search	Search for a file on this computer.
	Print	Print this datasheet view.
	Print preview	Preview the printing of this datasheet view.
	Spelling	Run the spell checker.
	Cut	Cut the selected text.
	Copy	Copy the selected text.
	Paste	Paste the text that was most recently cut or copied.
	Undo	Undo the last change made to the data.
	Insert hyperlink.	Insert a hypertext link into a field whose data type is hyperlink.
	Sort ascending	Sort the selected column in ascending sequence (from A to Z).
	Sort descending	Sort the selected column in ascending sequence (from Z to A).
	Filter by selection	See filters below.
	Filter by form	See filters below.
	Apply filter or Remove filter	See filters below.
	Find	Provides find and replace features - allowing you to search for a specific text value in the data being displayed and to replace this data value with another data value.
	New record	Moves the cursor to the asterisk row, where you can enter a new data row.
	Delete record	Delete the data row associated with the current cursor position.
	Datbase window	Display the database window. When the child windows are maximized, this restores the size of all child windows currently opened. It then displays the database window as the top child window.
	New object: autoform	Clicking on this button will result in an Access Form object being automatically created using this table. Clicking on the drop down arrow displays a menu showing the different Access objects you can created using this table. AutoForm and AutoReport will automatically create a form or report using this table. The other menu options allow you to create one of the Access objects listed in the database window.
	Help	Display the Access Help panel.

Filters

Two types of filters may be used in a datasheet view:

1. Filter by Selection

The filter is applied based on the field and data value that currently contains the cursor.

For example, to see only those Students that live in NY, click on any of the ny State values then click on the Filter by Selection button .

When a filter has been performed, the funnel button is active and it now means "remove filter". Clicking on the remove filter button will redisplay all of the data rows that were displayed before the filter was applied.

2. Filter by Form

This filter is applied by entering filter criteria into a filter form

Click on the filter by form button to dislay the following filter form.


• Clicking on the close button will close this filter form and redisplay the datasheet view.
• Clicking on the clear grid button will remove any filter criteria from the filter form. The picture above shows an empty filter form.
• Clicking on the funnel button will apply the filter criteria specified in the filter form.

Applying the filter of an empty filter form results in all of the data rows being displayed.

To enter filter criteria, you may either type a value into an apppropriate textbox or you can click on a drop down arrow and select a value from the list. Whey you type a value, you may also use an asterisk as a wildcard character. The asterisk means "following by zero or more of any characters".

Filter by form examples

1. List all students whose first name begins with an M.

1. Click the clear grid button to remove any prior filter criteria.
2. Enter m* in the First Name filter by form field. The filter by form will look like:
3. Strike the tab key to move the cursor over to the Last Name filter by form field. Microsoft Access changes the first name filter criteria to:
4. Click on the apply filter button.

Only those data rows whose first name value begins with an M will be displayed, for example:
2. List all students whose first name begins with an S AND live in New Jersey.

1. Click the clear grid button to remove any prior filter criteria.
2. Enter s* in the First Name filter by form field.
3. Strike the tab key to move the cursor over to the State filter by form field.
4. Enter nj in the State filter by form field.
5. Strike the tab key to move the cursor over to the Zip Code filter by form field.
6. The resulting filter by form criteria looks like:
7. Click on the apply filter button.

Only those data rows whose first name value begins with an S AND whose state value is nj be displayed, for example:
3. List all students whose first name begins with an S OR live in New Jersey.

1. Click the clear grid button to remove any prior filter criteria.
2. Enter s* in the First Name filter by form field.
3. Strike the tab key to move the cursor over to the State filter by form field. The filter by form criteria looks like:
4. Click on the Or tab found at the bottom of the filter by form window. In the above picture, the Or tab is immediately to the right of the Look For tab.
5. Enter nj in the State filter by form field.
6. Strike the tab key to move the cursor over to the Zip Code filter by form field. The filter by form criteria looks like:

At this point, the filter by form criteria says: Look for First Name Like "s*" Or State equals "nj".

7. Click on the apply filter button.

Only those data rows whose first name value begins with an S OR whose state value is nj be displayed, for example:

Note that Sally Spade, who lives in New York, is displayed in these filter results, but is not displayed in the results associated with example B above. Why?

• In example B, the student must have a first name starting with S AND must live in New Jersey. Sally Spade does have a first name beginning with an S, but does not live in New Jersey, and so does not meet the second part of the AND criteria.
• In example C, the student must either have a first name starting with S OR must live in New Jersey. Since Sally Spade does have a first name beginning with an S, she meets the first part of the OR criteria and thus meets the entire OR criteria.