11

Microsoft Access 2002Microsoft Access 2002

Tutorial 5 – Enhancing a Tutorial 5 – Enhancing a Table’s Design, and Creating Table’s Design, and Creating

Advanced Queries and Advanced Queries and Custom FormsCustom Forms

22

New Data Files!New Data Files!

Don’t work on the file you created so Don’t work on the file you created so farfar

Go to ’Tutorial.05’ folderGo to ’Tutorial.05’ folder

33

Preventing Input ErrorsPreventing Input Errors

Different techniquesDifferent techniques Lookup WizardLookup Wizard

Pick value from listPick value from list Input MaskInput Mask

Input is always on correct form, can’t enter Input is always on correct form, can’t enter letters in number field etc.letters in number field etc.

Validation CriteriaValidation Criteria Anything can be entered, but only specific Anything can be entered, but only specific

values are accepted by Accessvalues are accepted by Access

44

A Lookup Wizard field in a A Lookup Wizard field in a tabletable

To make a form easier to use, you will To make a form easier to use, you will often add a Lookup Wizard to the form. often add a Lookup Wizard to the form.

Instead of remembering a particular value, Instead of remembering a particular value, you can pick the value from a list. you can pick the value from a list.

This approach insures that you cannot This approach insures that you cannot enter an invalid value.enter an invalid value.

When you create the Lookup Wizard, you When you create the Lookup Wizard, you will provide a list of acceptable values will provide a list of acceptable values from which other users will make a from which other users will make a selection.selection.

55

A lookup field in Datasheet A lookup field in Datasheet viewview

When the user selects a description, Access will return the correct code that corresponds to the description. This figure below shows what the Lookup field looks like in the table datasheet. In this example, the NAICS description is displayed.

When you select a description, Access will lookup the code that goes with the descriptions.

66

Input mask for a table fieldInput mask for a table field

Some fields require special characters to Some fields require special characters to make them more readable. make them more readable. Example: phone number, Example: phone number, ((920920)) 424 424--01660166

Create an input mask that will display Create an input mask that will display these characters as a predefined format. these characters as a predefined format.

This predefined format can be used to This predefined format can be used to enter or display data in a field. enter or display data in a field.

If If literal display characters are defined, the literal display characters are defined, the user will not need to enter those user will not need to enter those characters.characters.

Use the Input Mask WizardUse the Input Mask Wizard

77

Input Mask character Input Mask character descriptionsdescriptions

This figure shows the input mask characters that are available and the meaning of each character.

More characters/options available. Press F1 to see all.

88

Data ValidationData Validation

DemonstratDemonstrate in Jobs e in Jobs databasedatabase

99

Pattern Match QueryPattern Match Query

You may want to view only records that match a You may want to view only records that match a particular criteria. You can do this by creating a particular criteria. You can do this by creating a query that uses a pattern match. query that uses a pattern match.

Wildcard charactersWildcard characters **: Match with any number of characters: Match with any number of characters ??: Match a single character: Match a single character ##: Match a single digit : Match a single digit

Use with Use with likelike operator operator Like ”508*”Like ”508*” Like ”#ab?cd*” (give an example of a string that matches)Like ”#ab?cd*” (give an example of a string that matches)

Can also use with Can also use with inin operator operator In (”ME”, ”NH”, ”WI”)In (”ME”, ”NH”, ”WI”) In (”Ma*”, ”DE?”)In (”Ma*”, ”DE?”) Not In (”ME”, ”MA”)Not In (”ME”, ”MA”)

1010

A design grid with a A design grid with a pattern match fieldpattern match field

This figure illustrates a criteria that specifies that only records whose phone numbers begin with 508 will be included in the datasheet. Notice that the * following the ‘8’ creates a wildcard, meaning that anything that follows the 508 is acceptable.

1111

A list of values expand the A list of values expand the selection possibilitiesselection possibilities

This design grid shows a field with a list of values. As long as the State value in any record is one of those in the list, the record will be included in the datasheet.

1212

Use both the And and Or Use both the And and Or logical operators in the logical operators in the

same querysame query There will be circumstances where you will want to There will be circumstances where you will want to

use both the And and Or logical operators in the use both the And and Or logical operators in the same query. same query.

The In operator naturally creates an Or condition. The In operator naturally creates an Or condition. You can also use the key word Or in the criteria row You can also use the key word Or in the criteria row to create an Or condition. to create an Or condition.

Placing two conditions on the same row of the Placing two conditions on the same row of the Criteria in the Query design grid creates an And Criteria in the Query design grid creates an And condition. condition.

Placing two conditions on separate rows in the Placing two conditions on separate rows in the Query design grid creates an Or condition.Query design grid creates an Or condition.

1313

And and Or conditions in the And and Or conditions in the same querysame query

Placing conditions in the same criteria row creates an AND condition. In this example, both criteria also create OR conditions, resulting in a match condition using AND and OR.

1414

Result for the combined Result for the combined use of And and Oruse of And and Or

The criteria for the State field specified that the State must be either MA or NH. The second criteria, for the Position Title field, specified that the values must be Waiter/Waitress or Kitchen Help. The criteria, in this case, uses the Or operator. This criteria could have also been created by placing the two positions on separate lines in the query design grid.

1515

Create a parameter queryCreate a parameter query

Sometimes when you create a query, you don't Sometimes when you create a query, you don't know exactly which records the user might want to know exactly which records the user might want to see. see.

To allow flexibility in the query, you can create a To allow flexibility in the query, you can create a parameter query. parameter query.

The parameter query will prompt the user to enter The parameter query will prompt the user to enter the value they want to use to select records. the value they want to use to select records.

Once the user has supplied this information, those Once the user has supplied this information, those records that match the value will be displayed in records that match the value will be displayed in the query datasheet.the query datasheet.

1616

Defining a parameter queryDefining a parameter query

The figure below shows a parameter query placed in the criteria row.

Note that the parameter query is enclosed in brackets. This text will be the user prompt when the query is run.

When the parameter query is run, a dialog box will be displayed asking the user to enter a value that they want to specify for the criteria.