Access Querying Using Criteria

1
Access Querying Using Criteria

• By
• Bob Larson

2
Objectives

• Use text and numeric data in criteria
• Use compound criteria in queries

3
Using Single Criteria

• Use text, numbers and dates in Criteria row
• Typed jones in the example
• Access added to indicate text
• signs around dates
• Nothing on numbers

Note Access is not case sensitive but many
programs are.
4
Using Multiple Criteria

• Using multiple criteria in same row (AND
  operation)
• Requires both to be true both accountants and
  grade 7

5
Using Criteria OR Operation

• Putting criteria on multiple rows
• Or row (all remaining rows)
• Allows all accountants and all grade 7 employees
  to display
• Equivalent to asking for all accounts OR all
  grade 7s
• Putting smith, jones and larson on separate rows
  under Lname
• Would display everyone with those last names
• Same result as typing smith or jones or larson in
  one cell

6
Multiple Criteria Be Careful

• Example would include
• All Jones and Larson regardless od department
• Only Smiths that are accountants
• If we wanted only accountants
• Acct would have to be entered on one row, or
• Could have typed smith or jones or larson in same
  row as acct

7
Relational Operators
Can be used with numbers, dates and text gtm
would return all names starting with m-z, but
would not include just the letter m

• gt Greater than
• lt Less than
• gt Greater than or equal to
• lt Less than or equal to
• ltgt Doesnt equal

8
Relational Operators (Dates)

• Example entered as lt1/1/1972
• Date can be in most valid format like gtDec 1
  1973
• Access converts to default format and adds

9
Between Operator

• Typed between 1/1/07 and 1/15/07
• Both values included
• Column could be widened to display entry, or
• Right click on entry and choose Zoom from popup
• Opens Zoom box for viewing or editing

Same asgt1/1/2007 and lt 1/15/2007
10
Not and Null Operators

• Put not in front of any criteria to reverse the
  logic
• Returns everything but the criteria
• Not acct below all Depts except acct
• Sometimes easier to define what you dont want
• Null means empty no entry
• Not the same as zero (0)
• Handy for finding missing information
• Example all non-accountants missing grade

11
In Operator

• Allows anything matching a comma-delimited list
• Example returns anyone that is a grade 7, 9 or 15
• Same result as entering 7 or 8 or 15
• Can be used with text fields in(wa,or,ca,id)
• Doesnt work with dates

12
Wildcards (Pattern Operators)

• Two wildcard characters
• multiple characters
• s - any name beginning with s
• son any name ending in son
• ing any name containing ing including
  beginning and end
• ? single character
• ?ll? any 4 letter name with two Ls in the
  middle
• s?n any name ending in son, sen, sin, sun (s
  any character n)

Note Access added like to indicate not an exact
match but following this pattern
13
Wildcards - Multiple

• In the example, entered
• Lname j
• Fname j
• Dept p
• Grade gt15
• All have to be true to be included

14
Fin