Your WHERE clause would resemble the following:įor more information about how to create criteria to use in a WHERE clause, see the article Examples of query criteria. To combine data from two data sources based on values in fields that have different data types, you create a WHERE clause that uses one field as a criterion for the other field, by using the LIKE keyword.įor example, suppose that you want to use data from table1 and table2, but only when the data in field1 (a text field in table1) matches the data in field2 (a number field in table2). You cannot create a join between fields that have different data types. For example, a field in one table may have a Number data type, and you want to compare that field to a field in another table that has a Text data type. Sometimes you may want to combine data sources based on fields that have matching data, but have different data types. Use the WHERE clause to combine data sources You can use other comparison operators, such as greater than (>) or less than (100. Note: You do not have to base the criteria in your WHERE clause on the equivalence of values. Others are complex, and use functions and special operators, and include field references. Some criteria are simple, and use basic operators and constants. This criterion can be applied to any kind of field to show records where the field value is null.Īs the previous table illustrates, criteria can look very different from each other depending on the data type of the field to which the criteria apply and your specific requirements. Only records where the number of years between a person's birth date and today's date is greater than 30 are included in the query result. You can put any information thats available to you into a table. A table is just a collection of data thats organized into rows and columns.
This criterion applies to a Date/Time field, such as BirthDate. Use Table to Store Data: In Microsoft Access you must break all your information into data thats stored in tables. The following table shows some sample criteria and explains how they work. A query criterion is similar to a formula - it is a string that may consist of field references, operators, and constants. When you want to use data to limit the number of records that are returned in a query, you can use criteria. In a SQL statement, the WHERE clause specifies criteria that field values must meet for the records that contain the values to be included in the query results.įor an overview of Access SQL, see the article Access SQL: basic concepts, vocabulary, and syntax. This article describes how to write a WHERE clause, and uses examples to illustrate various techniques that you can use in a WHERE clause. This is one of a set of articles about Access SQL. Access for Microsoft 365 Access 2021 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007 More.