ch04.htm

Microsoft_Access_97_Quick_Reference.zip

			to create a <I>filter</I> for a <I>table</I>, you often might want to see which records
			have no values. For example, you might want to find all addresses without zip codes
			so you can look them up before you do a mailing. You may also want to find which
			fields have <I>zero-length</I> <I>strings</I>.</P>
			<P>When you use a <I>null</I> <I>field</I> in a calculation, the result of a calculation
			is null. You can use the NZ <I>function</I> to convert nulls to zeros. (See &quot;Blanks,
			Empty, and Null: Work With&quot; in the Calculations part of this book.)


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>For a field to be capable of accepting
			zero-length strings, that field <I>property</I> must be set to Yes. (See &quot;Data:
			Blanks, Nulls, and Zero-Length Strings&quot; in the Table and Database Design part
			of this book.) 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> In <I>Design View</I> of a <I>query</I>, click in the <I>criteria</I>
				row under the desired field.
				<P>
				<DD><B>2. </B>To find all <I>records</I> with no entry, enter <B>Null</B>. Access
				will translate this to <TT>Is Null</TT>. If you want to find all records except blanks,
				type <B>Not Null</B> (which Access enters as <TT>Is Not Null</TT>).
				<P>
				<DD><B>3. </B>To find records with zero-length strings, enter <B>&quot;&quot;</B>
				(two quotes with no space).
				<P>
				<DD><B>4.</B> In the Field cell of the design <I>grid</I>, if you are calculating
				values that have nulls in some of the fields, enclose each field name in the <TT>NZ([</TT>Fieldname<TT>])</TT>
				function.
				<P>
				<DD><B>5. </B>Choose the <I>Datasheet View</I> button to see the results of the query
				or choose the Run button to update records if the query is an action query.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>To display zeros for a numeric field
			that has nulls, type the following in the Field cell of the Query Design grid: <B>NZ([<I>Fieldname</I>])+0</B>.
			
<HR>
</P>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>When you sort ascending on a field
			with Nulls, the records with the Null fields display first. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Criteria: Date</B></FONT></H2>
			<P>When you are working with date <I>fields</I>, there are some <I>criteria</I> you
			might want to keep in mind. You can choose to show just today's date or a specific
			month, day, or year. Another option is to include a range of dates.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> In <I>Design View</I> of a <I>query</I> or <I>filter</I>, click in
				the criteria row under the desired field.
				<P>
				<DD><B>2.</B> If you want to enter a specific date, type the date. Regardless of
				the format for the date, you can enter the date in its simplest format for the criteria:
				For example, <B>1/1/98</B>. When you leave the criteria cell Access translates this
				to <TT>#1/1/98#</TT>.
				<P>
				<DD><B>3.</B> If you want to find all records with today's date, type <B>Date()</B>.
				If you want all those after today's date, type <B>&gt;Date()</B>. If you want all
				those up to and including today's date, type <B>&lt;=Date()</B>.
				<P>
				<DD><B>4.</B> If you want all records between two days use the Between And <I>expression</I>
				as in the example: <B>Between 1/1/97 and 3/5/98</B>.
				<P>
				<DD><B>5. </B>To find all records for a specific month, year or day, use the <B>*</B>
				(asterisk). <B>1/*/98</B> finds all records in January 1998. <B>*/*/92</B> finds
				all records in 1992.
				<P>
				<DD><B>6. </B>Choose the <I>Datasheet View</I> button to see the results of the query
				or choose the Run button to update records if the query is an action query.
			</DL>
			<P>For date <I>functions</I>, see also &quot;Functions: Date&quot; in the Calculations
			part of this book.
			<H2><FONT COLOR="#000077"><B>Criteria: Multiple</B></FONT></H2>
			<P>If you are searching for data that meets more than one criteria, you can use the
			<I>Query</I> Design <I>grid</I> or the keywords <B>AND</B> and <B>OR</B>.</P>
			<P>To enter <I>criteria</I>, be sure you are in the Design View of a query or <I>filter</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>To set criteria for multiple fields where all criteria must be met
				(for example, <B>City is Denver</B> and <B>Address includes Broadway</B>), type the
				<I>expression</I> for the first <I>field</I>, move to the second field in the same
				criteria row and type the second expression. Repeat for all necessary fields.
				<P>
				<DD><B>2. </B>To set criteria for multiple fields where any criteria can be met,
				type the expression for the first field, move down to the next criteria row (labeled
				<TT>Or</TT>) and type the second expression under the second field. Move down again
				if there is a third criteria.
				<P>
				<DD><B>3.</B> If you are looking for multiple possibilities in the same field, type
				in one criteria cell and type <B>OR</B> between each expression (for example: <B>CO
				or NE</B>). If you have many entries you can use the <B>IN</B> keyword (for example,
				<B>IN(CO, AL, NE, KS</B>).
				<P>
				<DD><B>4. </B>If one field must meet two criteria use the <B>AND</B> keyword (for
				example <B>&gt;5 AND &lt;10</B>).
				<P>
				<DD><B>5. </B>Choose the Datasheet View button to see the results of the query or
				choose the Run button to update records if the query is an action query.
			</DL>
			<H2><FONT COLOR="#000077"><B>Criteria: Numeric</B></FONT></H2>
			<P>When you are looking for numeric data, there are some handy expressions for use
			with numeric <I>criteria</I>. To enter criteria, be sure you are in the criteria
			cell of the <I>Design View</I> of a <I>query</I> or a <I>filter</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Type the number in the criteria cell to see just this value.
				<P>
				<DD><B>2. </B>Type <B>&lt;</B>Number<B> or &lt;=</B>Number to see all numbers less
				than or less than or equal to the number.
				<P>
				<DD><B>3. </B>Type <B>&gt;</B>Number<B> or &gt;=</B>Number to see all numbers greater
				than or greater than or equal to the number.
				<P>
				<DD><B>4. </B>Type <B>Between </B>Number1<B> and </B>Number2 to find all numbers
				between two numbers (including both numbers).
				<P>
				<DD><B>5. </B>Type <B>&gt;</B>Number1<B> and &lt;</B>Number2 to find all numbers
				between two numbers (not including both numbers).
				<P>
				<DD><B>6. </B>Choose the <I>Datasheet View</I> button to see the results of the query
				or choose the Run button to update records if the query is an action query.
			</DL>
			<H2><FONT COLOR="#000077"><B>Criteria: Specify</B></FONT></H2>
			<P>You can enter selection <I>criteria</I> into <I>filters</I> and into queries.
			Queries are more generally useful in that they can work with related tables of <I>records</I>
			and direct the output of the result set to several different places. Filters apply
			only to the current <I>table</I> or <I>query</I> and cannot be used elsewhere.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Move to the criteria row and enter the <I>expression</I> into the design
				<I>grid</I> of the Query or Filter.
				<P>
				<DD><B>2.</B> If you cannot see the entire criteria, press Shift+F2 to expand the
				cell into a window. Choose OK when finished editing the criteria.
				<P>
				<DD><B>3. </B>If you want to enter complicated criteria, click the Build button on
				the toolbar and use the Expression <I>builder</I>.
			</DL>
			<P>Access allows for a wide range of values and expressions, and the Query <I>Design
			View</I> is a powerful tool for composing queries and applying criteria for selection.
			For some of the criteria types, see the other Criteria <I>sections</I> in this part.
			(See also &quot;Expression: Create with Builder&quot; in the Calculations part of
			this book.) Access translates the query into <I>SQL</I> and applies it to the appropriate
			<I>data sources</I> (tables and grids) that the query operates on.
			<H2><FONT COLOR="#000077"><B>Criteria: Text</B></FONT></H2>
			<P>Most of your data will be text <I>data type</I>. There are some handy expressions
			for use with text criteria. To enter criteria you need to be in the criteria cell
			of the <I>Design View</I> of a query or a <I>filter</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Enter in the complete text match you want. Access puts quotes around
				the <I>expression</I>.
				<P>
				<DD><B>2. </B>Include the asterisk (<B>*</B>) wild-card character for any text you
				are not sure of. A single asterisk can take the place of any number of characters.
				You can also use multiple asterisks in one expression. <B>Jo*ns*n</B> will find Johnson,
				Johanson, Jonsen, and Johnsen. Access enters <B>Like &quot;Jo*ns*n&quot;</B> in the
				criteria cell.
				<P>
				<DD><B>3. </B>Enter <B>Not</B> and then the text you don't want to match. <B>Not
				NY</B> will find all states except NY.
				<P>
				<DD><B>4. </B>Use the question mark (<B>?</B>) as a wildcard character for one letter.
				For example, <B>C?</B> would find CO, CT, and CA.
				<P>
				<DD><B>5. </B>Choose the <I>Datasheet View</I> button to see the results of the query
				or choose the Run button to update records if the query is an action query.
			</DL>
			<H2><FONT COLOR="#000077"><B>Criteria: Use Another Field</B></FONT></H2>
			<P>In some instances you want to use one <I>field</I> in a <I>criteria</I> for finding
			<I>records</I> based on another field. For example, you might want to find all records
			where the Promised Date is less than the Shipped Date and send the clients an apology.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>In <I>Design View</I> of a <I>query</I> or <I>filter</I>, move to the
				criteria row of the field.
				<P>
				<DD><B>2. </B>Type any operators and then the second field's name in square brackets.
				For example, <B>&lt;[ShippedDate]</B>.
				<P>
				<DD><B>3. </B>Choose the Datasheet View button to see the results of the query or
				choose the Run button to update records if the query is an action query.
			</DL>
			<H2><FONT COLOR="#000077"><B>Duplicates: Find</B></FONT></H2>
			<P>One of the most frustrating parts about managing data is trying to dispose of
			duplicates. Access can show which records have exact duplicates. You can then decide
			which <I>record</I> to delete. Before you create a <I>primary key</I> on a <I>table</I>
			with existing data, you also need to search for and remove duplicates.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>On the Queries tab of the <I>Database window</I>, choose <U>N</U>ew
				and double-click Find Duplicates Query Wizard.
				<P>
				<DD><B>2. </B>On the first step of the wizard, double-click the table or query.
				<P>
				<DD><B>3. </B>Double-click the <I>field</I> (or combination of fields) that will
				identify duplicates; choose <U>N</U>ext.
				<P>
				<DD><B>4. </B>Double-click any additional fields you want to display in the query;
				choose <U>N</U>ext.
				<P>
				<DD><B>5. </B>Give your query a name and click the <U>F</U>inish button.
			</DL>
			<P>The query will list only duplicated records in the table. You can modify the fields
			or delete the unnecessary records. (See &quot;Records: Delete&quot; in the Database
			Essentials part of this book.)


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Access cannot show you which records
			have close matches. For example, if you have <TT>491 Fox St</TT> and <TT>491 Fox</TT>
			<TT>Street</TT>, Access will not show these as duplicates. You might want to develop
			standard rules for entering data (always spell out or use abbreviations, for example).
			
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Duplicates: Remove</B></FONT></H2>
			<P>You can delete duplicates one at a time from the result of a Find Duplicates Query
			Wizard. (See &quot;Duplicates: Find&quot;.) However, if you have many duplicates,
			you might want to use the following procedure. This procedure does not allow you
			to choose which <I>record</I> of the two duplicates you want to delete.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>In the <I>Database window</I> select the <I>table</I> and click the
				Copy and Paste buttons on the toolbar. When prompted copy the structure only and
				give the new table a name.
				<P>
				<DD><B>2. </B>Open the new table in Design View, hold down Ctrl, click the <I>field</I>(s)
				with duplicated information, and click the <I>Primary Key</I> button on the toolbar.
				Save and close the new table.
				<P>
				<DD><B>3. </B>Select the first table and choose <U>Q</U>uery from the New Object
				button. Include all fields from the table in the query <I>grid</I> by double-clicking
				the asterisk in the Field List.