Conditions


We will next go into more detail about the various operators that we can use in a condition.

The comparison operators

 

These operators compare the value of one expression with the value of another. Independantly of the operator if one of the values is null, the result of the comparison will be null (neither true nor false). The comparison operators we can use are:

 

Operator

Meaning

=

equal to

<>

different to

<

less than

<=

less than or equal to

>

more than

>=

more than or equal to

   

 

The Between operator

 

Has the following syntax:

Expression Between value1 and value2

Examine whether the value of the expression is included between the two values defined by value1 and value2. Normally the expression would be the name of a field.

E.g:

[aulaclic_date] between #01/01/60# and #04/06/62#, in the QBE box would be expressed:

 

The In operator

 

Has the following syntax:

Expression In (value1, value2,...)

Examine whether the value of the expression is one of the values included in the list of values written between the parenthesis.

E.g to select the students from California or New York the search criteria may be: State In ("CA","NY")

 


The Is Null operator

 

When a column that intervenes in a condition contains the null value the result of the condition is neither true nor false, but null, no matter what the test is. Therefore if we want to list all the rows that have no value in a specific column, we can not use the column = no condition, we need to use a special operator, the operator Is Null.

It has the following syntax:

Expression Is null, where expression would normally be the name of a column.

E.g we want to know the students they have no state, the condition will be State Is Null.


The Like operator

 

Is used when we want to use wildcard characters when we want to compare a value to a pattern.

E.g we want to visualize the students whose names end in 'o'. In this case we need to use the wildcard character * and use the Like operator so that Access recognises the * as a wildcard character and not as the asterisk character. The condition would be name like '*o'.

The value that contains the wildcard character is the pattern and has to be enclosed in inverted commas (single or double).

The syntax is the following:

Expression Like 'pattern'

 

In the following table shows the characters that can be used in a pattern.

Pattern characters

Meaning

?

Single character

*

Zero or more characters

#

Single digit (0-9)

[characterslist]

Any character from the characterslist

[!characterslist]

Any character not included in the characterslist

   

E.g we want to know the students they have a zip code that begins with 46 followed by any three digits, the condition could be [zipcode] like '46###'

Characterslist represents a list of characters and can include almost any kind of character, including digits, characters are written one behind another without spaces. E.g to extract the names that begin with a, g, r or v the pattern would be '[agrv]*'.

Within a Characterslist we can include -, to indicate a range of values (From.. to ..), for example to extract the names that begin with a, b, c, d or e the pattern would be '[abcde]*' or '[a-e]*'.

The special characters: open brackets [, question ?, hash #, and asterisk * stop functioning as wildcard characters when they go between brackets. E.g to look for the names that begin with asterisk, the pattern would be: '[*]*' in this case the first * within the pattern does not act as a wildcard character but as any other character because it is between the brackets.

The close bracket ] can be used outside of a character list as an independant character, but may not be used in a characters list. E.g the 'a]*' pattern allows names to be found that start with an a followed by a ].

The []sequence of characters is considered a zero-length string ('' '').

Multiple intervals can be included between brackets, without needing to demarcate.

The hyphen - only defines an interval if it appears between two characters within two brackets, it represents a hyphen character.

 

   
   
Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.