Operators

Access and VBA provide six categories of operators that you can use to create expressions:

  • Arithmetic operators perform addition, subtraction, multiplication, and division.

  • Assignment and comparison operators set values and compare values.

  • Logical operators deal with values that can only be true or false.

  • Concatenation operators combine strings of characters.

  • Identifier operators create unambiguous names for database objects so that you can assign the same field name, for example, in several tables and queries.

  • Other operators, such as the Like, Is, In, and Between operators, simplify the creation of expressions for selecting records with queries.

Operators in the first four categories are available in almost all programming languages. Identifier operators are specific to Access; the other operators of the last category are provided only in RDBMSs that create queries based on SQL. The following sections explain how to use each of the operators in these categories.

Arithmetic Operators

Arithmetic operators operate only on numeric values and must have two numeric operands, with the following exceptions:

  • When the minus sign ( ) changes the sign (negates the value) of an operand. In this case, the minus sign is called the unary minus.

  • When the equal sign (=) assigns a value to an Access object or a VBA variable identifier.

Table 10.1 lists the arithmetic operators that you can use in Access expressions.

Table 10.1. Arithmetic Operators

Operator

Description

Example

+

Adds two operands

Subtotal + Tax

Subtracts two operands

Date - 30

(unary)

Changes the sign of an operand

-12345

*

Multiplies two operands

Units * UnitPrice

/

Divides one operand by another

Quantity / 12.55

\

Divides one integer operand by another

Units \ 2

Mod

Returns the remainder of division by an integer

Units Mod 12

^

Raises an operand to a power (exponent)

Value ^ Exponent

Jet/VBA operators are identical to operators used by all versions of BASIC. If you aren't familiar with BASIC programming, the following operators need further explanation:

Operator

Description

\

The integer division symbol is the equivalent of "goes into," as used in the litany of elementary school arithmetic: 3 goes into 13 four times, with 1 leftover. When you use integer division, operators with decimal fractions are rounded to integers, but any decimal fraction in the result is truncated.

Mod

An abbreviation for modulus, this operator returns the leftover value of integer division. Therefore, 13 Mod 4, for example, returns 1.

^

The exponentiation operator raises the first operand to the power of the second. For example, 2 ^ 4, or two to the fourth power, returns 16 (2*2*2*2).

These three operators seldom are used in business applications but often occur in VBA program code.

T-SQL

SQL Server supports all Jet/VBA operators, except ^ (exponentiation). T-SQL substitutes % for Mod.

Assignment and Comparison Operators

Table 10.1 omits the equal sign associated with arithmetic expressions because in Access you use it in two ways neither of which falls under the arithmetic category. The most common use of the equal sign is as an assignment operator; = assigns the value of a single operand to an Access object or to a variable or constant. When you use the expression = "Q" to assign a default value to a field, the equal sign acts as an assignment operator. Otherwise, = is a comparison operator that determines whether one of two operands is equal to the other.

Comparison operators compare the values of two operands and return logical values (True or False) depending on the relationship between the two operands and the operator. An exception is when one of the operands has the Null value. In this case, any comparison returns a value of Null. Because Null represents an unknown value, you cannot compare an unknown value with a known value and come to a valid True or False conclusion.

Table 10.2 lists the comparison operators available in Access.

Table 10.2. Comparison Operators

Operator

Description

Example

Result

<

Less than

123 < 1000

True

<=

Less than or equal to

15 <= 15

True

=

Equal to

2 = 4

False

>=

Greater than or equal to

1234 >= 456

True

>

Greater than

123 > 123

False

<>

Not equal

123 <> 456

True

The principal uses of comparison operators are to create validation rules, to establish criteria for selecting records in queries, to determine actions taken by macros, to create joins using the SQL-89 WHERE and SQL-92 JOIN clauses, and to control program flow in VBA.

Logical Operators

Logical operators (also called Boolean operators) are used most often to combine the results of two or more comparison expressions into a single result. Logical operators can combine only expressions that return the logical values True, False, or Null. With the exception of Not, which is the logical equivalent of the unary minus, logical operators always require two operands.

Table 10.3 lists the Jet/VBA logical operators.

Table 10.3. Logical Operators

Operator

Description

Example 1 Example 2

Result 1 Result 2

And

Logical and

True And True

True

  

True And False

False

Or

Inclusive or

True Or False

True

  

False Or False

False

Not

Logical not

Not True

False

  

Not False

True

Xor

Exclusive or

True Xor False

True

  

True Xor True

False

The logical operators And, Or, and Not are used extensively in Access expressions and SQL statements; in SQL statements these operators are uppercase, as in AND, OR, and NOT. Xor is seldom used in Jet or VBA. Eqv (equivalent) and Imp (implication) are rarely seen, even in programming code, so Table 10.3 omits these two operators.

T-SQL

T-SQL has conventional AND, OR, and NOT logical operators. Xor is supported by the ^ bitwise comparison operator. Other T-SQL bitwise operators are & (bitwise and) and | (bitwise or).

Concatenation Operators

Concatenation operators combine two text values into a single string of characters. If you concatenate ABC with DEF, for example, the result is ABCDEF. The ampersand (&) is the preferred concatenation operator in VBA and Jet. Concatenation is one of the subjects of "The Variant Data Type in Jet and VBA" section later in the chapter.

Tip

Don't use the + symbol to concatenate strings in queries or Jet SQL. In Jet SQL and VBA, + is reserved for the addition of numbers; & concatenates literals and variables of any field data type. The & operator performs implicit type conversion from numbers to text; the & operator treats all variables as character strings. Thus 1234 & 5678 returns 12345678, not 6912.


T-SQL

T-SQL uses the + symbol for string concatenation. The SQL-92 specification, however, designates two vertical bars (pipe symbols) as the official concatenation operator, as in 'String1' || 'String2'. The string concatenation symbol is one of the least consistent elements of common flavors of SQL.

Identifier Operators

Earlier versions of Access used identifier operators, !(the exclamation point, often called the bang operator) and .(the period, called the dot operator in VBA). As of Access 2000, the period replaces the bang operator, which Access 2003 continues to support for backward compatibility. The period operator performs the following operations:

  • Combine the names of object classes and object names to select a specific object or property of an object. For example, the following expression identifies the Personnel Actions form:

     Forms.HRActions 

    This identification is necessary because you might also have a table called HRActions.

  • Distinguish object names from property names. Consider the following expression:

     TextBox1.FontSize = 8 

    TextBox1 is a control object, and FontSize is a property.

  • Identify specific fields in tables, as in the following expression, which specifies the CompanyName field of the Customers table:

     Customers.CompanyName 

T-SQL

T-SQL uses the bang operator (!) as an alternative to NOT, as in !< (not less than) and !> (not greater than). This usage isn't compliant with SQL-92.

Other Operators

The remaining Jet operators are related to the comparison operators. These operators return True or False, depending on whether the value in a field meets the chosen operator's specification when used in a WHERE clause criterion. A True value causes a record to be included in a query; a False value rejects the record. When you use these operators in validation rules, entries are accepted or rejected based on the logical value returned by the expression.

Table 10.4 lists the four other operators used in Access queries and validation rules.

Table 10.4. Other Operators

Operator

Description

Example

Is

Used with Null to determine whether a value is Null or Not Null

Is Null Is Not Null

Like

Determines whether a string value begins with one or more characters (for Like to work properly, you must add a Jet (DOS) wild card, * or one or more ?s)

Like "Jon*" Like "FILE????"

In

Determines whether a string value is a member of a list of values

In("CA", "OR", "WA")

Between

Determines whether a numeric or date value lies within a specified range of values

Between 1 And 5

You use the wildcard characters * and ? with the Like operator the same way that you use them in DOS. The * (often called star or splat) takes the place of any number of characters. The ? takes the place of a single character. For example, Like "Jon*" returns True for values such as Jones or Jonathan. Like "*on*" returns True for any value that contains on. Like "FILE????" returns True for FILENAME, but not for FILE000 or FILENUMBER. Wildcard characters can precede the characters that you want to match, as in Like "*son" or Like "????NAME".

T-SQL

T-SQL supports the IS, LIKE, IN, and BETWEEN logical operators, as described in Table 10.4. However, T-SQL uses the single quote (') as the string identifier, rather than Jet's default double quote (").

Except for Is, the operators in this other category are equivalent to the SQL reserved words LIKE, IN, and BETWEEN. Jet includes these operators to promote compatibility with SQL. You can create each of these operators by combining other VBA operators or functions. Like "Jon*" is the equivalent of VBA's InStr(Left(FieldName, 3), "Jon"); In("CA", "OR", "WA") is similar to InStr("CAORWA", FieldName), except that matches would occur for the ambiguous AO and RW. Between 1 And 5 is the equivalent of >= 1 And <= 5.

Tip

Always use Between...And, not the >= and <= comparison operators, to specify a range of dates. You must repeat the field name when using the comparison operators, as in DateValue >= #1/1/1999# And DateValue <= #12/31/1999#. Between syntax is shorter and easier to understand, as demonstrated by DateValue Between #1/1/1999# And #12/31/1999#.




Special Edition Using Microsoft Office Access 2003
Special Edition Using Microsoft Office Access 2003
ISBN: 0789729520
EAN: 2147483647
Year: 2005
Pages: 417

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net