`
`
`
`
`
`Merrill Communications LLC
`d/b/a Merrill Corporation
`Exhibit 1005 pt 7
`
`
`
`Sorting, Searching,
`Filtering, and Printing
`
`n this hapter we' lllo kat ways to
`snrl (alphab tize), seard1 f r, repla
`filter, and print data . Tl1ese opera-
`tions are available in both datasheet view and form vi w, althougll you may prefer to
`experiment with them from datasheet v i w wh r lit
`ffects Me most obvi0us.
`
`,
`
`Sorting [Alphabetizing] Your Data
`
`Sorting data simply means to put it into some meaningful order. For example, we often
`sort lists and address books alphabetically to make it easy to find information. When
`you're working with paper, sorting is a tedious and boring process. But with Access, all
`it takes is a few mouse clicks.
`
`Quick and Easy Sorting
`
`Here's how to do a quiCk, simple sort based on any field in your table (except a Hyper(cid:173)
`link, Memo or OLE Object field):
`
`1. Open your table, query, or form in datasheet view or form view (as appropriate).
`2. Click on the field on which you want to base the sort. For example, to put
`employee names into alphabetical order, click on the LastName column selector
`(in datasheet view) or the LastName field (in form view).
`
`
`
`310
`
`CHAPTER 9 • SORTIN G, SEARCHING, FILTERING, AND PRINTING
`
`3. Do one of the following:
`• To sort records in ascending order (smallest-to-largest or A to Z), click on the
`Sort Ascending toolbar button (shown at left) or choose Records )l- Sort )l- Sort
`Ascending. Or right-click on the column selector (in datasheet view) or the cur(cid:173)
`rent field (in form view) and choose Sort Ascending from the shortcut menu.
`• To sort records in descending order (largest-to-smallest or Z to A), click on the
`Sort Descending toolbar button (shown at left) or choose Records )l- Sort )l- Sort
`Descending. Or right-click on the column selector (in datasheet view) or the cur(cid:173)
`rent field (in form view), and choose Sort Descending from the shortcut menu.
`
`In datasheet view, you can easily verify the results by reading down the column that
`you based the sort on (see Figure 9.1). The results of the sort won't be so apparent in
`form view because you can see only one record at a time. But if you scroll through the
`records, you'll see that you're now scrolling though them in a sorted order. (If you're
`viewing a form, you can switch from form view to datasheet view to verify the sort.)
`
`Last Names in original order
`
`Last Names in sorted (alphabetical) order
`
`•
`
`s· ooris
`6! Lane
`7 JO$eph
`B' Helerr
`(AuloNumber)
`Record:
`, ·,..-----:-•.,
`
`6 ~ane
`~ rAmi
`3 Emlly
`4 Albert
`7fJose~h
`2 Ahd reV'!_
`8 Helen
`(AlJ L.olllo,J.mber)
`
`Lift
`Nlktog
`Nlktog
`~ip pl~
`
`IVan Noslo
`
`Wheels
`
`MUdQ;IQM
`The original
`Employees table
`and the sorted
`Employees table
`shown in
`datasheet view.
`We changed
`the sample
`data that
`Access provides.
`
`Sorts within Sorts
`Sometimes you may need to sort on two or more fields. For example, if a table has many
`names, you might want to sort on the LastName and the FirstName fields. That way,
`surnames will be in alphabetical order, and within each name, they'll be sorted by the
`person's first name as shown in Figure 9.2.
`Sorting on two or more fields is easy:
`
`1. Open the table or query in datasheet view. Or if you're currently viewing a form in
`form view, switch to datasheet view.
`
`
`
`• ilflll;llifM
`Sorting an
`Employees table
`on two fields
`and on a
`single field.
`
`SORTING (ALPHABETIZING) YOUR DATA
`
`1311
`
`•
`
`Tille
`Sale s Reptesantallve
`..
`Qfti~e Manll_QI!r
`Sales l~e nre sent ative
`Sales Representative
`I S_ales Manager
`Vice President, M rkei!I'IQ
`!Vice Prosrdenl, Sales
`, Produt l Eva ngel!~
`
`'Anita
`Emily
`Albert
`J ~oseph
`Andrew
`Helen
`
`Records are sorted by
`LastName only (Niktogs
`are not in alphabetical order
`by FirstName) .
`
`Records are sorted
`by LastName and FirstName.
`
`2. Move the fields that you want to use to the leftmost column positions (see Chapter 8)
`if necessary. (The fields you're sorting on must be in adjacent columns.) Access will do
`the sort from left to right. Thus to sort by surname and then by first name, arrange the
`columns with the LastName column just to the left of the FirstName column.
`3. Select the column(s) you want to base the sort on . To do so, click on the first col(cid:173)
`umn and then Shift-click on additional column(s). In the example below, we
`moved the LastName and FirstName fields to th e leftmost column positions and
`then selected those fi elds for sorting:
`
`
`
`312
`
`CHAPTER 9 • SORTING, SEARCHING, FILTERING, AND PRINTING
`
`4. Click on the Sort Ascending or Sort Descending toolbar button (depending on the
`order you want) or use the equivalent menu options or shortcut menu options
`given earlier.
`
`Returning the Records to Their Original Order
`
`When you 're ready to return the records to their original, unsorted order, choose
`Records~ Remove Filter/Sort. Or right-click anywhere in the datasheet or form and
`choose Remove Filter/Sort.
`
`Any time you close the table after using the sort feature (described above) or the
`~-..:. filter feature (described later in this chapter), Access will ask if you want to save
`changes to the design of your table. If you choose Yes, Access stores the current
`sort and filter in the table's Filter and Order By properties.
`
`To learn more about sorting, go to the Access Help Contents, open the Finding And
`Sorting Data book, open the Sorting Data In Tables, Queries, And Forms book, and then
`choose a sorting subtopic that interests you.
`
`Finding Individual Records
`
`Scrolling through records is fine for browsing. But when you're in a hurry, you'll prob(cid:173)
`ably want to use these search techniques to find specific data in your table:
`
`1. Open the table, query, or form that contains the data you're looking for (if it isn't
`already open). You can use either datasheet view or form view-it doesn't matter
`which.
`2. If you want to search only one specific field, click on that field. The Find feature
`can search fields that have any data type except Yes/No, OLE Object, or Lookup.
`3. Click on the Find toolbar button (shown at left). Or choose Edit ~ Find or press
`Ctrl+F. Or right-click on the column selector (in datasheet view) and choose Find
`from the shortcut menu. You'll see the Find In Field dialog box shown below.
`
`Fond in held 'lastName'
`
`iJ EJ
`
`Match:
`
`I whole FJeld
`
`E1 1;1 So"'ch 0~ WI~ Field
`
`F.QCmal.led
`
`
`
`FINDING INDIVIDUAL RECORDS
`
`1313
`
`4. Type the text you want to look for in the Find What text box (for example, Niktog).
`To make the search more general, you can include the wildcard characters listed in
`Table 9.1.
`
`TABLE 9.1: WILDCARD CHARACTERS YOU CAN USE IN THE FIND WHAT TEXT BOX
`
`WILDCARD
`
`STANDS FOR
`
`EXAMPLE
`
`Cll "' 11:1 .a
`....
`11:1
`11:1
`0
`11:1
`c:n c
`
`~
`11:1
`
`~ u
`
`Sm?th matches Smith,
`Smyth, Smath, and so on.
`Sm* matches Smith, Smith-
`ereens, Sm'ores, Sm .
`9## Oak St. matches any
`addresses in the range of 900
`Oak St. to 999 Oak St.
`Sm[iy]th matches Smith or
`Smyth, but not Smath.
`[N-Z] matches any text start-
`ing with the letters N
`through Z, provided that
`you also select Match ~ Start
`Of Field.
`[!N-Z] matches any text that
`doesn't start with the letters
`N through Z, provided that
`you also select Match ~ Start
`Of Field.
`1111 matches zero-length
`strings, provided that you
`also select Match ~ Whole
`Field.
`Null matches empty fields,
`provided that you also select
`Match ~ Whole Field and
`have not selected (checked)
`Search Fields As Formatted.
`
`I
`
`?
`
`*
`
`#
`
`[ ]
`
`Any single character
`
`Zero or more characters
`
`Any numeric digit
`
`Any characters in
`the brackets
`Any characters within the
`range (must be within
`brackets)
`
`Any character except
`(must be within brackets)
`
`1111 (two double-quotes)
`
`Zero-length strings
`
`Null or Is Null
`
`An unformatted blank field
`
`
`
`314 I CHAPTER 9. SORTING, SEARCHING, FILTERING, AND PRINTING
`
`S. Select or deselect the Match Case, Search Fields As Formatted, and Search Only
`Current Field options; choose a search direction (Up, Down, or All) from the Search
`drop-down list; and choose a match option (Whole Field, Any Part Of Field, or Start
`Of Field) from the Match drop-down list as needed. Note: If you're searching in a
`lookup field and the Match box is set to Whole Field or Start Of Field, be sure to
`select (check) Search Fields; otherwise the search will fail.
`
`By default, the Find and Replace features do a "Fast Search" in which Access
`searches the current field and matches the whole field. To change the default set(cid:173)
`ting, choose Tools > Options, click on the Edit/Find tab in the Options dialog
`box, and then choose an option under Default Find/Replace Behavior. For more
`details, see Chapter 15 or click on the ? button in the Options dialog box and
`then click on the Edit/Find option you're curious about.
`
`6. Start the search by clicking on the Find First button (to find the first occurrence of
`the text in the table) or the Find Next button (to find the next occurrence relative
`to the cursor position).
`
`Access will find the first record (if any) that matches your request. If the dialog box
`is covering data that Access has found, drag the box out of the way.
`Repeat steps 4 through 6 (or click on Find Next) until you've found the record you
`want. If Access tells you it can't find the search item, click on OK to end the search.
`When you're done searching, click on Close or press Esc to close the dialog box.
`
`After closing the Find dialog box, you can press Shift+F4 to find the next occur(cid:173)
`rence of text you last searched for.
`
`For more help on finding records, go to the Access Help Contents, open the Finding
`And Sorting Data book, open the Finding Records Or Data book, and then choose the sub(cid:173)
`topic you're interested in.
`
`Fixing T~pos Automatic all~
`
`If you're not such a hot typist, you'll be happy to know that Access can check and correct
`your spelling automatically as you type, or anytime you wish. You'll learn next about
`the AutoCorrect and Spelling features, which can search out typing mistakes and ren(cid:173)
`der them harmless.
`
`
`
`FIXING TYPOS AUTOMATICALLY
`
`1315
`
`The AutoCorrect and Spelling features in Access are almost identical to the ones
`you'll find in other Microsoft Office programs, including Word and Excel.
`
`Correcting Mistakes As You Type
`
`If you frequently find yourslef typng (oops, yourself typing) certain words incorrectly, or
`you often want to replace abbreviations (such as CEFGW) with their longer forms
`(Close Enough For Government Work), the AutoCorrect feature can help you . You sim(cid:173)
`ply teach AutoCorrect the word or abbreviation it should replace and then provide its
`replacement word or phrase. In the future, Access will substitute the typo or abbrevia(cid:173)
`tion with its replacement word or phrase. AutoCorrect also can correct capitalization
`errors that occur when you type TWo INitial CApitals (instead of just one) in a word or
`you forget to capitalize names of days.
`
`Adding new words to the list of automatic replacements has no effect on existing text.
`
`Setting Up AutoCorrect Words and Settings
`To teach Auto Correct new words or to change the AutoCorrect settings, open any data(cid:173)
`base object or go to the database window.
`
`I. Choose Tools ,.. AutoCorrect. Figure 9.3 shows a sample AutoCorrect dialog box
`after we filled in the Replace and With boxes. (Notice that Access comes with an
`extensive list of commonly mistyped words that it can replace automatically.)
`2. Use these techniques to turn AutoCorrect features on or off:
`
`• To replace two initial capital letters with just one initial capital letter, select
`(check) Correct TWo INitial CApitals. Deselect this option to prevent auto(cid:173)
`matic replacement when you type two initial capital letters.
`• To capitalize the first letter of a sentence, select (check) Capitalize first letter of
`sentence. Deselect this option to keep Access from capitalizing the first letter in
`each sentence.
`• To capitalize names of days if you forget to do so, select (check) Capitalize
`Names Of Days. Deselect this option to prevent automatic capitalization when
`you type day names.
`• To have Access notice when you type with caps lock on by mistake, select
`(check) Correct accidental use of cAPS LOCK key.
`
`I I
`
`
`
`316
`
`CHAPTER 9 • SORTING, SEARCHIN
`
`, FILTERING, AND PRINTING
`
`MilftQ;tQM
`Use the Auto(cid:173)
`Correct dialog
`box to custom(cid:173)
`ize the way
`Access replaces
`text automati(cid:173)
`cally as you type
`into text or
`memo fields.
`Choose Tools ~
`AutoCorrect to
`get here.
`
`Replace
`these wor ds ...
`
`AutuConeol
`
`AutoCor red
`
`•
`
`fl £j
`
`P Cl!fl'e<t TWo INitial CApitals
`P Capitalize first letter of ~ntence.
`P Capitaize names of days
`P Corre<t eccldental use ri cAPS ~OCJ< l<ey
`rP Replace text as you type
`With:
`&eplace:
`IJJ~OSI!! e~lJQh lor gover'ronent wor~
`jCEFGW
`cafe
`i c~nt
`I cant ~ve been
`c•pt•l crly
`1
`•chong01g
`_j dl~~n~cter
`
`=
`
`cafe
`Corljt
`.,,, •. ·~.f been
`capitol cty
`ch""QSir~
`thM~er
`
`1
`
`111
`
`I
`
`add
`
`OK
`
`!;;xceptions... I
`
`____ ,J
`~:
`~·
`I UF.-Iete I =
`I Cancel I
`
`with these words
`
`• To replace words shown in the left column of the AutoCorrect dialog box with
`words shown in the right column, select (check) Replace Text As You Type. Dese(cid:173)
`lect this option to prevent automatic replacements as you type.
`
`3. Use these techniques to add, change, and delete words in the replacement list:
`
`• To add a new word to the replacement list, type the misspelling or abbrevia(cid:173)
`tion into the Replace text box. Then type the correction or expanded form of
`the word into the With text box. Click on Add to add the items to the list.
`• To change a word or its replacement, scroll to and then click on the appro(cid:173)
`priate word in the list below the Replace and With text boxes (the words are
`listed in alphabetical order). The Replace and With boxes will show the item
`you selected. Change th e Replace or With text as n eeded, and then click on the
`Add or Replace button (whichever is available) to update the list.
`• To delete a word and its replacement, scroll to and click on the appropriate
`word in the list. Then click on the Delete button.
`
`4. Repeat steps 2 and 3 as needed. When you're finished using the AutoCorrect dialog
`box, click on OK.
`
`
`
`FIXING TYPOS AUTOMATICALLY
`
`--..a ing Your Spelling" for details.
`
`You can add new words to the AutoCorrect list during a spell check. See "Check(cid:173)
`
`Using AutoCorrect during Data Entry
`As you type new text or paste a word or abbreviation into a text or memo field, Auto(cid:173)
`Correct will fix typos or expand abbreviations automatically. (AutoCorrect has no effect
`on existing text.) The automatic correction takes place when you type a space or punctu(cid:173)
`ation mark in the field or move the cursor to another field.
`If you n eed to cancel an automatic replacement as you're typing, press Ctrl+Z (or
`choose Edit> Undo AutoCorrect); the text will reappear as you typed it. To reinstate
`the automatic correction, press Ctrl+Z again (or choose Edit> Undo).
`Need more information? Go to the Access Help Contents, open the Working With
`Data book, open the Checking Spelling And Automatically Correcting Errors As You Type,
`and then explore the automatic correction subtopics as needed.
`
`Checking Your Spelling
`
`Access can check and correct the spelling in text or memo fields of a datasheet, or in
`selected text in a datasheet or form. The spell check ignores fields that don't have a Text
`or Memo data type.
`Here's how to start a spell check:
`
`1. Do one of the following, depending on how much text you want to spell check:
`
`• To check spelling in datasheet view, select the records, columns, fields, or
`text within a field.
`• To check spelling in form view, select the field or text within the field you
`want to check.
`• To check all text and memo fields in a table, go to the database window, click
`on the Tables or Queries tab, and then click on the table or query you want to
`check. Or open the table or query in datasheet view and select all the columns.
`
`2. Click on the Spelling toolbar button (shown at left) or choose Tools > Spelling or
`press F7. Spell checking will start immediately.
`3. Follow the screen prompts. Once spell checking begins, what you do next depends
`on what (if any) errors are found (see "Correcting or Ignoring a Word" below).
`4. Click on OK to clear the completion message when the spell check is complete.
`
`II
`
`
`
`318
`
`CHAPTER 9 • SORTING, SEARCHING, FILTERING, AND PRINTING
`
`Correcting or Ignoring a Word
`When the spell checker finds a word that isn't in its dictionary, it will highlight the
`word in your datasheet or form, suggest a correction if it can, and pause so you can
`decide what to do next. Figure 9.4 shows the Spelling dialog box after it tripped over
`the word Foosball, which isn't in its dictionary.
`You can click on any of these buttons (if they're available) when the spell checker is
`waiting for you to make a correction:
`
`(where xxx is a field name). Click on this button to ignore the
`Ignore 'xxx' Field
`named field during the current spell check. Spell checking will continue.
`Ignore or Ignore All Click on Ignore to ignore the current occurrence of the word
`shown in the Not In Dictionary box. Click on Ignore All to ignore all occurrences of
`the word shown in the Not In Dictionary box. Spell checking will continue.
`Suggest Click on the Suggest button, or type a word into the Change To box and
`then click Suggest, to see alternatives to the word shown in the Change To box. To
`copy one of the suggested words into the Change To box, click on the word you want
`to copy in the list next to Suggestions. You can repeat these steps as needed.
`Change or Change All Lets you replace one occurrence (Change) or all occur(cid:173)
`rences (Change All) of the word shown in the Not In Dictionary box with the
`word shown in the Change To box. If necessary, use the Suggest button described
`above or type a word into the Change To box or click on a word in the Sugges(cid:173)
`tions list to copy the word you want to use as the replacement word into the
`Change To box; then click on the Change or Change All button, as appropriate.
`Spell checking will continue.
`
`M#tftQ;liQM
`The Spelling
`dialog box after
`Access found a
`word that
`wasn't in the
`dictionary.
`
`•
`
`Spelhng
`
`£S
`
`Nat In Dictionary.
`Chango!o:
`
`Suggeslioos:
`
`jFoosball
`~=-__:=:=:=:::=----
`lgno~e 'ProductName' field I
`
`Ignore
`
`Ignore AI
`
`..:J _....;.&ld _ _.
`
`Add~onls To:
`
`r:::IC-us:-tom~D~,c-----3...,
`
`Aut.oCotrect
`
`Qptions... I._..:::.....:::........J __ c..nce~ _ __.
`
`
`
`FIXING TYPOS AUTOMATICALLY
`
`1319
`
`Add Lets you add the word shown in the Not In Dictionary box to the spelling
`dictionary that's shown in the Add Words To box. If necessary, choose a diction(cid:173)
`ary name from the Add Words To drop-down list before you click on the Add
`button. (Do not add words that truly are misspelled to your dictionary! See the
`sidebar titled "Maintaining Custom Dictionaries" for more details.)
`AutoCorrect Adds the word shown in the Not In Dictionary box to the Auto(cid:173)
`Correct dictionary and assigns the word in the Change To box as the replacement
`word. This button saves you the trouble of manually adding words that you com(cid:173)
`monly mistype to the AutoCorrect dictionary (as explained earlier in this chapter).
`
`If you add a word to the AutoCorrect dictionary accidentally, complete the spell
`check (or cancel it). Then choose Tools ~ AutoCorrect, highlight the incorrect
`word in the replacement list, and click on the Delete button.
`
`Options Takes you to th e Spell Options dialog box (shown below). From here
`you can choose which language Dictionary to use and choose whether to
`
`• Always Suggest alternate spellings or suggest them only when you click on the
`Suggest button
`• Suggest words From Main Dictionary Only, or offer suggestions from both
`the main dictionary and the custom dictionary
`• Ignore Words In UPPERCASE (such as ASPCA), or include those words when
`spell checking
`• Ignore Words With Numbers (such as RNAA47) or include those words when
`spell checking
`
`lflJ Options
`
`£I
`
`OK
`
`Cancel
`
`
`
`320
`
`CHAPTER 9 • SORTING, SEARCHING, FILTERING, AND PRINTING
`
`..
`
`Maintaining Custom
`Dictionaries
`You can create a custom dictionary any(cid:173)
`time you're using the Spelling dialog box.
`This trick can be handy if you want to set
`up a new custom dictionary for storing
`special-purpose terms. To create a custom
`dictionary:
`
`1. In the Spelling dialog box, erase any
`dictionary name that appears in the
`Add Words To combo box.
`2. Type a valid dictionary name and
`press Enter or choose an existing cus(cid:173)
`tom dictionary from the combo box's
`drop-down list. The default dictionary
`name is custom.dic, but you can use
`any name for your dictionary as long
`
`as that name ends with a period
`and the letters die. For example,
`mywords. di c and medical terms. di c
`are valid dictionary names.
`If Access asks for permission to create
`the dictionary, click on Yes.
`
`3.
`
`The custom dictionary is a plain text file
`that lists one word per line in alphabetical
`order by word. Custom dictionaries are
`stored in the folder \Program Files \Co11111on
`Fil es\Mi crosoft Shared\ Proof on drive C
`(if you chose the default installation loca(cid:173)
`tion). If you add a misspelled word to a
`custom dictionary by accident, you can
`use Windows Notepad or Windows Word(cid:173)
`Pad (in text mode) to delete the incorrect
`words. Edit carefully!
`
`After choosing options in the Spell Options dialog box, click on OK.
`
`Undo Last Lets you undo the most recent change the spell checker made. Click
`on this button as necessary to back out of changes one by one.
`Cancel Ends the spell check and returns you to the datasheet or form.
`
`For more details about spell checking, go to the Access Help Contents, open the
`Working With Data book, open the Checking Spelling And Automatically Correcting Errors
`As You Type, and then investigate the spelling subtopics.
`
`Replacing Data in Multiple Records
`
`In addition to letting you fix spelling errors in some or all text and memo fields, Access
`lets you instantly change the contents of a field throughout all (or some) of the records
`in your table. But before you even think of experimenting with this Replace feature,
`please make sure you understand the following warning.
`
`
`
`REPLACING DATA IN MULTIPLE RECORDS
`
`1321
`
`You can Undo only the last individual change that Replace makes. To play it safe,
`make a copy of the table before you replace data. That way, if you make a major
`mistake, you can close the table and then rename the unchanged original copy of
`the table to write over the version that has unwanted changes. See Chapter 1 for
`help on copying and renaming database objects.
`
`To replace data in a table:
`
`1. Open the table, query, or form that contains the data you want to change.
`2. (Optional) If you want to limit replacement to a single field, click on that field. The
`Replace feature can replace text in fields that have any data type except OLE Object,
`AutoNumber, and Lookup.
`3. Choose Edit~ Replace or press Ctrl+H. You'll see the Replace In Field dialog box
`shown below.
`
`Aeplu(:u m held "ProductName"
`
`::J r M..tch .t;ase
`r ~..n Wl:P!llif'"~kl
`R Search Only Cun_!!nt Field
`
`l] EJ
`l Eind Next I
`a~ I
`Aep\6oe81 I
`I
`
`Clote
`
`4. Type the value you want to search for and change in the Find What text box. You
`can use the wildcard characters listed in Table 9.1 if you wish.
`S. Type the replacement text in the Replace With text box.
`6. Select or deselect the Match Case, Match Whole Field, and Search Only Current
`Field options, and choose a search direction (Up, Down, or All) from the Search
`drop-down list as needed. Then do one of the following:
`
`• To make the change automatically (so that Access won't ask you to verify
`each occurrence), click on the Replace All button.
`• To verify the changes in each record, click on the Find Next button. (If neces(cid:173)
`sary, drag the dialog box out of the way so you can see what data Access is
`about to change.) Then if you do want to change the current record, click on
`Replace. If you don't want to change the current record, click on Find Next.
`Repeat this step as needed.
`
`
`
`322
`
`CHAPTER 9 • SORTING, SEARCHING, FILTERING, AND PRINTING
`
`7. Click on Yes to continue with step 9 or click on No to cancel the operation and
`return to steps 4, S, or 6.
`8. When Access cannot find any more matches, it will display a message. Click on OK
`to clear the message.
`9. Click on the Close button when you're done replacing records.
`
`Here are some tips you might want to know about:
`
`• To globally replace large amounts of data more quickly, or to perform calcula(cid:173)
`tions on data (for example, raising certain product prices by 10 percent), use an
`Update query rather than Replace. See Chapter 10 for more about Update queries.
`• To change the default settings for the Find and Replace features, choose Tools ).
`Options and click on the Edit/Find tab. Change the settings under Default Find/
`Replace Behavior as needed and then click on OK. Chapter 15 explains more
`about personalizing Access.
`
`A Search-and-Replace Example
`
`Suppose that several people enter data into your table. Some of them spell names such
`as Los Angeles in full, while others use abbreviations such as L.A. This type of inconsis(cid:173)
`tency is sure to cause problems down the road. Imagine that you want to send a form
`letter to all Los Angeles residents. If you isolate records that have Los Angeles in the
`City field, you 'll miss all the ones that contain L.A. Why? Because computers aren't
`smart enough to know that Los Angeles and L.A. mean the same thing.
`Anyway, the cure for your dilemma is to change all the L.A. entries in the City field
`to Los Angeles (or vice versa). To do that, click on the City field, choose Edit). Replace,
`and then fill in the dialog box this way:
`
`Replace m held 'Coty'
`
`F{ld\Nhat:
`
`2J P' Match.!;;ase
`P' Motch ~le Field
`P' Search 0 nly Currjlnt Field
`
`HEJ
`
`fmd NClll
`
`fieplace
`
`~
`
`To start the replacement, click on the Replace All button and bingo-you're done!
`Oust answer any questions that appear on the screen.)
`
`
`
`FILTERING OUT UNWANTED RECORDS
`
`1323
`
`To prevent the L.A. city names from creeping into your data in the future, you
`might want to add an AutoCorrect entry that changes "L.A." to "Los Angeles"
`automatically. See "Correcting Mistakes As You Type" earlier in this chapter.
`
`If you ever need reminders or help while replacing data, go to the Access Help Index
`and look up Replacing Values In Fields.
`
`Filtering Out Unwanted Records
`
`You can use filters to temporarily isolate (or select) records you want to see and to hide
`unwanted records. For example, you can focus on your California customers while
`hiding information about customers located in other states. There are several ways to
`create a filter:
`
`• Filter By Input lets you create a filter from a field's shortcut menu.
`• Filter By Selection and Filter Excluding Selection let you create a filter by select(cid:173)
`ing text or clicking in a field that contains the text you want to filter.
`• Filter By Form lets you create a filter by typing the values you're looking for into
`a fill-in-the-blanks form or datasheet.
`• Advanced Filter/Sort lets you use a window that's similar to a query design win(cid:173)
`dow to create a filter. You can choose each field to search or sort by and specify
`the sort order and values you're looking for.
`
`You can filter fields that have any data type except Memo or OLE Object.
`
`Table 9.2 compares the four filtering methods briefly, and the following sections
`explain how to use each one. If you still need more information, go to the Access Help
`Index and explore the subtopics under Filters.
`
`To swi tch between designing a Filter By Form and designing an Advanced Filter/
`Sort, open the Filter men u in the design window for either type of filter and then
`choose Filter By Form or Advanced Filter/Sort as appropriate.
`
`
`
`324 I CHAPTER 9. SORTING, SEARCHING, FILTERING, AND PRINTING
`
`I
`
`TABLE 9.2: FILTERING METHODS COMPARED
`
`.,
`
`Filter by
`(Or Excluding)
`Selection
`
`Yes (if you spec-
`ify the criteria one
`at a time)
`
`No
`
`No
`
`No
`
`Yes
`
`Yes
`
`Yes
`
`No
`
`Filter by
`Input
`
`Yes (if you
`specify the
`criteria one at
`a time)
`No
`
`Yes
`
`No
`
`If This Is
`What You
`Want To Do ...
`
`Find records
`that meet one
`criterion And
`other criteria
`Find records
`that meet one
`criterion Or
`other criteria
`Find records
`that contain
`expressions as
`criteria
`Find records
`and sort them
`in ascending or
`descending
`order at the
`same time
`
`Filter by
`Form
`
`Advanced
`Filter/Sort
`
`Yes
`
`Yes
`
`Yes
`
`Yes
`
`Filtering by Input
`Filter by Input is a new Access feature that lets you use a field's shortcut menu to find
`records. You can use it for filters, such as finding all the records with "CA" in the State
`field . Filter by Input also works for more complicated filters that use comparison oper(cid:173)
`ators, wildcards, or functions. For example, you can use ">t" in a filter on the Last Name
`field to limit the records you view to those that begin with letters after T. Or you can use
`"D*" in the First Name field to see all records for people whose first name begins with D.
`To use Filter by Input:
`
`1. Open the table, form, or query you want to filter.
`2. Right-click anywhere in the field you want to use for the filter except in the field
`header.
`3. Enter a value in the box after Filter For: and press Enter.
`
`
`
`FILTERING OUT UNWANTED RECORDS
`
`1325
`
`To see other examples of valid filter expressions, search the Access Help Index for Filters,
`Criteria to Select Records and then look up Examples of Expressions Used in Queries and Filters.
`
`Filtering by Selection or Exclusion
`Let's suppose you've found a record that contains some data you're looking for(cid:173)
`perhaps an order for tether balls in the Order Details table. Now you want to find all the
`other orders for that product, or all the orders for products except tether balls. Access
`makes jobs like these ridiculously easy:
`
`1. Open the table, query, or form you want to filter (in datasheet view or form view).
`2. Locate the record and field that contains an instance of the data you want Access
`to filter. For example, in the Order Details table, locate a record that displays the
`value Tether ball in the ProductiD field.
`3. Tell Access how much of the field to match:
`• To match the entire field, select the entire field or click in the field without
`selecting anything. Example: To match Tether ball (the entire field), click in a
`field that displays a value of Tether ball as shown below.
`
`• To match part of a field, starting with the first character in the field, select
`text starting at the beginning of the field (see below) . Example: To match fields
`that start with Foo, highlight Foo at the start of a field. Items such as Football,
`Fousball, and Foot Powder will match.
`
`Record: ..!!.l..!J I
`
`
`
`326
`
`CHAPTER 9 • SORTING, SEARCHING, FILTERING, AND PRINTING
`
`• To match part of a field, after the first character in the field, select text
`within the field. Example: To match fields that contain ball (preceded by a
`space), highlight the word ball and the space that appears before ball, as shown
`below. Items such as Crystal ball and Tether ball will match, but Football and
`Foosba/1 will not (because there's no space before ball in those item names).
`
`In the above example, Access will not match Golf balls when you apply the filter
`.__ ... because the pattern "ball" doesn't fall exactly at the end of the field. To isolate
`Golf balls along with Crystal ball and Tether ball, select "ball" in a record that con(cid:173)
`tains Golf balls in the ProductiD field. This forces Access to use a more general pat(cid:173)
`tern that says "Find the word ball preceded by a space anywhere in the field,"
`rather than the less general pattern "Find the word ball preceded by a space at
`the end of the field."
`
`4. Do one of the following to apply your filter:
`
`• To show only records that have matching values in the field, click on the Fil(cid:173)
`ter By Selection toolbar button (shown at left) or choose Records ~ Filter ~ Filter
`By Selection or right-click on the field and choose Filter By Selection.
`• To show only records that do not have matching