[ Pobierz całość w formacie PDF ]
.Chapter 11 explains how to create expressions.NoteNumeric and Currency data types are set automatically to 0 when you add a newrecord.Working with validationData validation enables you to limit the values that are accepted in a field.Validation may be automatic, such as the checking of a numeric field for text or avalid date.Validation can also be user-defined.User-defined validation can be assimple as a range of values (such as those found in the Length or Weight fields), orit can be an expression like the one found in the Gender field.Figure 5-14 (shown earlier) displays the property area for the Length field.Noticethe validation options for the Length field.The Validation Rule 100 in the cell.When you follow these steps, your query looks similar to the query shown in Figure13-7.When working with numeric data, Access doesn t enclose the expression withquotes, as it does with string criteria.Figure 13-7: Criteria set for weight of animals.Here the criteria isgreater than ( > )100.Numeric fields are generally compared to a value string that uses comparison oper-ators, such as less than (), or equal to (=).If you want to specify acomparison other than equal, you must enter the operator as well as the value.Remember that Access defaults to equal when an operator is not specified in crite-ria.That is why you needed to specify greater than (>) 100 in the previous examplequery for animals over 100 pounds. 3596-X ch13.F 5/30/01 11:37 AM Page 382Part III &' Using Access in Your Work382Working with Currency and Counter data in a query is exactly the same as workingwith Numeric data; you simply specify an operator and a numeric value.Entering Yes/No (logic) criteriaYes/No criteria are used with Yes/No type fields.The example data that you supplyin the criteria can be for only Yes or No states.You can also use the Not and theoperators to signify the opposite, but the Yes/No data also has a Null state that youmay want to check for.Access recognizes several forms of Yes and No.Table 13-5lists all the positive and negative values that you can use.Thus, instead of typing Yes, you can type any of these in the Criteria: cell: On, True,Not No, No, = 10/1/00 And [Visit Date] =100 And 99 And 100To limit a date field to dates before January 2002, you enterbutton to dis-play the next Import Text Wizard screen.This screen enables you to select each column of the Text Import grid, acceptor change the field name, decide whether it will be indexed, and set the datatype (which is also automatically determined by the Wizard), or even skipadding the field to the final table.As with the Spreadsheet Import Wizard, youmove from field to field by selecting the next field column once selectedyou can change its options also.You can choose to skip a column if you want.8.Click Next> to display the next Import Text Wizard screen.This screen enables you to choose a field for the primary key.You can enableAccess to create a new AutoNumber field (by choosing Let Access addPrimary Key), enter your own (by selecting Choose my own Primary Key andselecting one of the columns), or have no primary key.9.Click the option button that says Choose my own Primary Key and select thefield Medication Code.10.Click Next> to display the last Import Text Wizard screen.The last screen enables you to enter the name for the imported table and(optionally) run the Table Analyzer Wizard.11.Accept the default name of Medlimit and click Finish to import the delimitedtext file.Access creates a new table, using the same name as the text file s name, then it dis-plays an information box informing you that it created the table successfully; click-ing the OK button returns you to the database.The filename appears in the AccessDatabase window, where Access has added the table MEDLIMIT.Importing fixed-width text filesIn fixed-width text files, each field in the file has a specific width and position.Filesdownloaded from mainframes are the most common fixed-width text files.As youimport or export this type of file, you must specify an import/export setup specifi-cation.You create this setup file by using the Advanced options of the Import TableWizard.To import a fixed-width text file, follow these steps:1.Open the Access Import-Export database and select File ª' Get External Data ª'Import.2.In the Import dialog box, select Files of type: Text files.3.Double-click PETFIXED.TXT in the File Name list box.Access opens the firstscreen of the Import Text Options Wizard dialog box for the tablePETFIXED.TXT. 3596-X ch22.F 5/30/01 11:42 AM Page 730Part IV &' Advanced Access Database Topics730This screen displays the data in the text file and guesses whether the type oftext file is delimited or fixed width.As you can see, the Wizard has correctlydetermined that it s a fixed-width file.4.Click Next> to display the next Import Text Wizard screen.This screen makes a guess about where columns begin and end in the file,basing the guess on the spaces in the file.Notice that it has missed severalfields in the second column (combining them all together).Figure 22-24 shows that Access has not done a good job in this file.It has rec-ognized the first field correctly, but the second four fields have been lumpedtogether.You ll need to add field break lines in the structure.Figure 22-24: The Import Text Wizard s attempt tosplit the fields.Notice that several fields are mergedtogether in the second column.As you can see in Figure 22-24, you can drag a field break line, add one, ordelete one to tell Access where the fields really are.5.Move to position 32 (between the 08 and 1 of the first record named Bobo).6.Once the pointer is in position on the lined bar in the center, create a breakline by clicking at that position.If you make a mistake and put the line in the wrong place, simply highlight theline and either move it by dragging it or double-click it to delete it.7.Move to position 33 (between the 1 and M) and add another break.8.Move to position 34 (between the M and 20) and add another break.After you have accomplished Steps 5 through 8 you should have a breakbetween the end of the Date field, the beginning and end of the Type of Animalfield (1, 2, 3), the Gender field (M, F), and the beginning of the Weight field.Figure 22-25 shows the corrected field specifications for the table. 3596-X ch22.F 5/30/01 11:42 AM Page 731Chapter 22 &' Working with External Data731Figure 22-25: The field breaks now set correctly forthe fields of the fixed-width tableAs you use these tools to define the field widths, you re completing an inter-nal data table known as Import/Export Specifications.9.Click the Advance button to activate the Import Specification window for thePetfixed table.Figure 22-26 shows the Import Specification screen after clicking theAdvanced button in the Import Text Wizard
[ Pobierz całość w formacie PDF ]