MS-Access / Getting Started

---

BETWEEN Predicate

Compares a value with a range of values.

Syntax

<expression> [NOT] BETWEEN <expression> AND <expression>

Notes: The data types of all expressions must be compatible. Comparison of alphanumeric literals (strings) in Access or a default installation of SQL Server is case-insensitive.

Let a, b, and c be expressions. Then, in terms of other predicates, a BETWEEN b AND c is equivalent to the following:

(a >= b) AND (a <= c)

a NOT BETWEEN b AND c is equivalent to the following:

(a < b) OR (a > c)

The result is undefined if any of the expressions is Null.

Example

To determine whether the SoldPrice is greater than or equal to $100 and less than or equal to $500, enter the following:

SoldPrice BETWEEN 100 AND 500

See also Expression, SELECT Statement, Subquery, and WHERE Clause, in this tutorial.

Column-Name

Specifies the name of a column in an expression.

Syntax

[[[]{table-name | select-query-name |
correlation-name}[]].][[]field-name[]]

Notes You must supply a qualifier to the field name only if the name is ambiguous within the context of the query or subquery (for example, if the same field name appears in more than one table or query listed in the FROM clause).

The table-name, select-query-name, or correlation-name that qualifies the field name must also appear in the FROM clause of the query or subquery. If a table or query has a correlation name, you must use the alias, not the actual name of the table or query. (A correlation name is an alias you assign to the table or query name in the FROM clause.)

You must supply the enclosing brackets in an Access desktop application (.accdb) only if the name contains an embedded blank or the name is also a reserved word (such as select, table, name, or date). Embedded blanks and enclosing brackets are not supported in the ANSI standard. You can use names that have embedded blanks in SQL Server by including a SET QUOTED_IDENTIFIER ON command and then enclosing each nonstandard name in double quotes ("). When you open a query from an Access project, Access automatically includes this command in the command stream that it sends to SQL Server.

If the field-name is a multi-value field, a query referencing the field-name returns the individual values separated by commas. A query datasheet provides a combo box that you can use to edit the multiple values. If you bind the column to a combo box control on a form, you can edit the field on the form. To edit the individual values in separate rows, use fieldname. Value in your query. For records in the table that have multiple values in the field, the query returns one row per value. The effect is identical to linking to a related many-tomany lookup table using a join. Note, however, that when you ask for field-name.Value from more than one multi-valued column in a table, the resulting query is not updatable because the query returns the Cartesian product of the multiple values in the two fields for each row in the source table.

If the field-name is an attachment data type, a query datasheet provides an attachment control to allow you to edit the data. You can also edit the data if you bind the field to an Attachment control in a form. You can individually reference one of the three properties of an attachment field: field-name.FileData, field-name.FileName, or field-name.FileType. All three properties return one row per separate attachment for each record in the source table, but you cannot update the values. The FileData property returns the binary attached file, the FileName property returns the original name of the file, and the FileType property returns the file extension.

Examples

To specify a field named Customer Last Name in a table named Customer List in an Access desktop application (.accdb), use the following:

[Customer List].[Customer Last Name]

To reference the same column in a view, stored procedure, or function for SQL Server, use the following:

"Customer List"."Customer Last Name"

To specify a field named StreetAddress that appears in only one table or query in the FROM clause, enter the following:

StreetAddress

To reference the individual values of a multi-valued field named ContactType, enter the following:

ContactType.Value

See also FROM Clause, SELECT Statement, and Subquery in this tutorial.