1 Microsoft Access 2 Specifying Query Criteria 3

1 Microsoft Access

2 Specifying Query Criteria

3 Exact Matches and Literal values

4 Text • Literal values for text must be enclosed in quotes: Example: "Smith"

5 Number, Currency • Number and currency values should NOT be in quotes: Example: 9. 70

6 Date/Time • Date/Time values should be surrounded by hash signs (AKA number or pound signs) Example: #7/30/2005#

7 Inexact Matches

8 Relational operators Description • greater than Example >10 • less than <10 • greater than or equal to >=10 • less than or equal to <=10 • Not equal to <>10

9 between. . . and. . . • between. . . and. . . between 3 and 5 includes 3, 4 and 5 between #1/1/2005# and #3/10/2006# includes all dates between Jan. 1 2005 and March 10, 2006 between "cohen" and "cramden" inlcudes all text which sorts alphabetically between "cohen" and "cramden"

10 in ( ) • Examples – in(3, 5, 6) – in("cohen", "cramden", "smith", "jones") – in(#1/1/2005#, #1/1/2006#)

11 Wildcards

12 LIKE • Like is used to match patterns of values Example: Like "s*" Will match all values that start with an "s"

13 Wildcards • A wildcard is the pattern that specifies what will be matched.

14 Wildcard characters • Wildcards use special characters to specify the types of information to match: see next few slides

15 asterisk: * • asterisk ( * ) - matches any number of any character (including zero characters) examples on next slide. . .

16 examples of * • like "s*" Matches all values that start with "s". (e. g. "smith", "schwartz", but not "cohen" or "davies") • like "*s" Matches all values that end with "s" (e. g. "davies" but not "smith" or "schwartz") • like "c*n" Matches all values that start with "c" and end with "n". (e. g. "cohen", "cramden", "cohan" and "cohain") • like "c*a*n" Matches all values that start with c, have an "a" somewhere in the middle and end with an "n". (e. g. "cohain" and "cohan") • like "c*an" Matches all values that start with "c" and end with "an". (e. g. "cohan" but not "cohain")

17 question mark: ? • question mark ( ? ) matches a single character. • Examples like "a? ? " matches "ape", "aaa" and "ark" but not "apple". like "*a? " matches "bread" and "lean" but not "apple" and not "fella"

18 number sign: # • number sign ( # ) matches a single numerical digit • Examples: like "###abc" matches "123 abc" and "456 abc" but not "a 123 abc" and not "abc 123" and not "1234 abc". (there are 3 number signs)