Introduction to query criteria
A criterion is similar to a formula — it is a string that may consist of field references, operators, and constants
When you want to limit the results of a query based on the values in a field, you use query criteria. Micrsoft Access also refers to these as Expressions.
Criteria can look very different from each other, depending on the data type of the field to which they apply and your specific requirements. - Some criteria are simple, and use basic operators and constants.
- Others are complex, and use functions, special operators, and include field references.
Useful links for more information:
Simple criteria for all data types:| Criteria Name | Write it like... | Function | | Equals | "x" | Searches for values equal to x | | Does Not Equal | Not in ("x") | Searches for all values except those equal to x | | Null | Is Null | Searches for empty fields | | Not Null | Is Not Null | Searches for non-empty fields |
Simple criteria for text:| Criteria Name | Write it like... | Function | | Contains | Like "*x*" | Searches for all values that contain x | | Does Not Contain | Not like "*x*" | Searches for all values except those that contain x | | Begins with | Like "x*" | Searches for all values beginning with x | | Ends with | Like "*x" | Searches for all values ending with x | | Comes After | >= "x" | Searches for all values that come afterx in alphabetical order. | | Comes Before | <= "x" | Searches for all values that come before x in alphabetical order. |
Simple criteria for numbers:| Criteria Name | Write it like... | Function | | Between | Between "x" and "y" | Searches for values in the range between x and y | | Less Than | < x | Searches for all values smaller than x | | Less Than or Equal To | <= x | Searches for all valuessmaller than or equal to x | | Greater Than | > x | Searches for all values larger than x | | Greater Than or Equal To | >= x | Searches for all values larger than or equal to x |
Simple criteria for dates:| Criteria Name | Write it like... | Function | | Between | Between "#mm/dd/yy#"and "#mm/dd/yy#" | Searches for dates that fall between two dates. | | Before | <#mm/dd/yy# | Searches for dates before a certain date | | After | >#mm/dd/yy# | Searches for dates after a certain date. | | Today | =Date() | Searches for all records containing today's date | | Days Before Today | <=Date()-x | Searches for all records containing dates x or more days in the past |
 
|
|
