Example of using a Criteria in Query: MS Access

Example of using a Criteria in Query

1)  =, >, <, >= , <= , <> (not equal) comparison operator1

These operators can be used for number, currency, date and text of data type field. The meaning is the same meaning for mathematic operator.

operator

 

 

 

 

 

 2) Using “Is null”

It is used as a criteria to get the empty data of that field. For example, you want to get a list of task that has not completed or finished. You can put the “Is null” in the criteria on FinishDate field.

isnull

 

 

 

 

dateResult

 

 

 

 

3) Using “Is not null”

Is Not Null operator works in opposite direction from the “Is Null” operator. We can use to get a list of task completed by put “Is Not Null” on the criteria of FinishDate field as shown in the picture below:

notnull2

 

 

 

 

 

 

4) Using Date() and Date()-1

        Date() is used in criteria to get today date like put in the DueDate field to see if any task due today (4/28/14)

today

        Date()-1  is today date -1 day is yesterday. If today is 4/28/14 then Date() -1 is 4/27/14.

yesterday

 

 

 

 

 

 

5) Use # for Date data type

We use the pound sign (#) in the front and end of date to get the value from the date data type field. Example below is a criteria to get the list of task start from 3/1/14 until now.

dateisnullResult

 

 

 

 

6) Create new field in query

A new field can be created with a new name followed by colon( : ) and a reference a format to a main field name. for instance, creating a new field name YearOfStart from StartDate field with format as year 2014 and a Project Name as copy of field TaskName.

newfield

 

 

 

newfieldResult

 

 

 

 

 

7) Format month as 1, Jan, January

The previous example shows the year format as 4 digit of year number (2014). The format of month is similar method.

formatmon

 

 

 

 

formatMonResult

 

 

 

 

 

Example of month format:

M = 1,2,3,..3

Mm = 01,02,03,….

Mmm = Jan, Feb, Mar, …

Mmmm = January, February, March, …

Sometime we want to sort the month from January to December as 1-12, we can use the format of “mm” to get a number of 01 – 12 and sort it Ascending or Descending as shown on the above example.

 8) Format Day “ddd” for Mon, Tue, Wed, … or “dddd” for Monday, Tuesday, Wednesday,…

daydayResult

 

 

 

 

9)  Format Date as “d” = 1,8,15,27, or    “dd” = 01,08,15,27

dateNumdateNumResult

 

 

 

 

10) Enter parameter

We use a bracket […] and put a message inside the bracket for instruction what to do.

We put [Enter a year ex ‘2013’] to tell use to enter a year in the YearStart field that has a format of 4 digit year (2013).

parameter

 

 

 

 

parameterResult

We can use two separate parameters on two separate fields in criteria. For instance, we want to get list of task that started and finished from the specific date and we want the users to enter their own date. Below is an example of using two parameter on date fields.

        a. Enter two parameters on StartDate and FinishDate field

startdate

 

 

 

 

        b.  Enter a year number with a 4-digit number format as ‘2013’

twoparameteraffiliate_link

 

      c. Get the result

twoparameterResult

 

 

 

11) “Like” operator

Like is mostly used in the text data field and follow by question mark (?) and the asterisk or star (*) in the front or back or both front and back of the keyword. The question mark (?) represents a single character only.

like

 

 

 

 

 

 

Like can also be used in the parameter entry as example below:

        a. Enter like with parameter on the criteria

likepara

 

 

 

 

         b. Enter “m” in the parameter

likeparameter

          

 

 

 

c. Get result of customer list with name starts with “M” only

likeparaResult

 

 

 

12 ) “AND” operator

andAND operator can be used for text, number and date field. The result of AND will be narrowed down to match the parameter in the criteria. AND can be used inside one field or on two or more fields. AND can be used by entering more than one criteria in the same line, but different field name as show below:

andResult

 

 

 

13) “OR” operator

orOR operator can be used for text, number and date field. The result of OR will be combined from the result of either match the 1st criteria or the 2nd criteria. It can be used inside one field or on two or more fields. OR can be used by entering more than one criteria in different field in one query, but must entered in a different line for each criteria as show below:

orResult

 

 

 

 

14) Between…..And

The Between…And can be used in the text, number and date field. The syntax of operator is like >= x <=

Criteria Entry
Result
Between 1 and 100 Display a list that has number from 1 to 100
Between #1/1/14# and #12/31/14# Display a list that has a date start from1/1/14 to 12/31/14 or whole year of 2014
Between “M*” And “T*” Display a list of name that start with letter M to letter T

It can be used with the parameter entry from the user as shown on the example below:

betweendate

 

 

 

enterbegindate

 

 

 

 

15) Link to field on the opened form

The criteria of field in the query can be linked to that field in the form. The form that linked must be opened in order to have the query works. For instance, we need a query to show a record of the customer that shows in the Customer form, the criteria in the Customer_id will be “[Forms]![Customer]![Customer_id].” The query links the customer record to Customer form by Customer_id as screen shot below:

customerForm
linkformcustomercustomerlinkeResult

 

 

16) Rename field name: A field name can changed in the query. The format of changing new name is a new name followed by colon (:) and the old field name as an example below of changing a field name CustomerName to Customer.

renameCust

 

 

 

renameResult

 

 

 

 

More Info At:

Microsoft website

http://www.fontstuff.com/access/acctut06.htm
affiliate_link

Related posts