🗊Презентация Выражения в Access

Глава 4. Выражения в Access

План 4.1. Операторы 4.2. Константы 4.3. Функции 4.4. Построитель выражений

При работе с различными объектами в Access широко используются выражения — аналог формул в Excel. Выражение — это любая комбинация операторов, констант, функций и идентификаторов1, результатом которой является некоторое значение. Константы, функции и идентификаторы, используемые в выражениях, называются операндами. Выражения часто употребляются для проверки различных условий и проведения вычислений в таблицах, запросах, формах и отчетах.

Они позволяют выполнять действия с числами, датами и текстовыми значениями в каждой записи, используя данные из одного или нескольких полей. Например, с помощью выражения можно перемножить значения двух числовых полей или объединить несколько текстовых значений. Несколько примеров выражений было приведено ранее в главе 2 при описании условий на значения полей таблицы.

4.1. Операторы

В выражениях применяются следующие типы операторов: • арифметические операторы — используются для выполнения математических вычислений; • операторы конкатенации — используются для слияния строк; • операторы сравнения — используются для выполнения операций сравнения; • логические операторы — используются для выполнения логических операций; • операторы идентификации — создают однозначные имена объектов БД.

4.1.1. Арифметические операторы Операнды должны быть выражениями, имеющими числовое значение. Для изменения приоритета арифметических операций используются круглые скобки. Если хотя бы один из операндов является выражением со значением Null, то результат имеет значение Null. Оператор деления нацело «\» округляет оба операнда до целых значений, а затем делит первый на второй. Результат округляется до целого, например, 11 \ 2 = 5; 7,6 \ 2,5 = 4. Оператор Mod также округляет оба операнда до целых значений и делит первый на второй. Результат — остаток от деления. Например, 9 Mod 2 = 1, а 7,6 Mod 4 =0.

4.1.2. Операторы слияния строк (конкатенации) Операторы & (амперсанд) или + создают текстовую строку, присоединяя содержимое второй строки к концу первой. Если один из операндов — число, то он преобразуется перед проведением операции слияния в строку символов.

Для объединения строк лучше использовать оператор &, а не +, так как если одна из участвующих в операции строк, например <строка 2>, имеет значение Null, то результат операции <строка 1> + <строка 2> равен Null, , а результат операции <строка1> & <строка 2> равен <строка 1>. Например, в выражении "Число заказов = " & [Число заказов] объединяются строка символов и значение поля Число заказов. Если число заказов равно 100, то результатом выполнения операции будет строка"Число заказов = 100".

4.1.3. Операторы сравнения Оператор сравнения сравнивает значения двух операндов и возвращает в качестве результата одно из логических значений: True или False. Если хотя бы один из операндов является выражением со значением Null, то результат имеет значение Null. Кроме стандартных операторов сравнения, приведенных в таблице 4.2,в Access имеется еще четыре дополнительных оператора сравнения: Is, In, Between и Like, которые обычно используются для проверки условия назначение в поле или в условиях отбора записей в запросе.

При использовании вместе с Null определяет, является ли значение Null или Not Null. Например, Is Null применяется для отбора записей, имеющих в данном поле значение Null, а Is Not Null — для отбора записей, имеющих в данном поле непустое значение

Оператор In Осуществляет проверку значения на совпадение с элементом из заданного списка. Элементы списка отделяются друг от друга точкой с запятой. Текстовые значения должны браться в кавычки, например, In("Москва";"Киев";"Минск") или In(2;4;6;8).

Оператор Between Осуществляет проверку, находится ли числовое значение внутри заданного диапазона. Например, Between 10 And 20 означает, что значениедолжно находиться в интервале [10, 20].

Оператор Like Осуществляет проверку значения на соответствие заданному шаблону.  

В таблице перечислены специальные символы, используемые в шаблоне, и соответствующие им символы в сравниваемом выражении. При проверке совпадения символов их регистр роли не играет. Чтобы включить в список диапазон символов, нужно указать первый символ, знак дефиса и затем последний символ, например [К-Р].

4.1.4. Логические операторы Обычно применяются для объединения двух или нескольких условий в единое целое. Ниже приведены наиболее часто используемые логические операторы.

Результат выполнения операции логического умножения And равенTrue, если значения всех операндов равны True; в противном случае он равен False. Результат выполнения операции логического Or равен True, еслиз начение хотя бы одного операнда равно True; в противном случае он равенFalse. Результат выполнения операции логического отрицания Not равенTrue, если значение операнда равно False; в противном случае он равенFalse.  

4.1.5. Операторы идентификации. Идентификаторы Часто в выражениях используются значения полей таблиц, элементов управления форм и других объектов БД. Имена полей в разных таблицах или элементов управления в формах могут совпадать. Чтобы Accessправильно вычислил значение выражения, необходимо обеспечить однозначность ссылок в выражениях на объекты БД и их свойства. Access использует два оператора идентификации "!" (восклицательный знак) и "." (точка).

Чаще в идентификаторах встречается оператор "!". Он используется для ссылок на объекты1. При ссылке на поле таблицы он служит для отделения имени поля от имени таблицы. Сами имена заключаются в квадратные скобки, и ссылка имеет следующий вид: [<имя таблицы>]![<имя поля>].

Это так называемая полная форма записи идентификатора поля таблицы. Если нет неопределенности в ссылке, то допустима и неполная форма записи идентификатора поля таблицы в виде [<имя поля>]. Так, в запросах, использующих одну таблицу, обычно используется неполная ссылка на поле. Например, полная ссылка на поле Фамилия в таблице Студент имеет вид [Студент]![Фамилия], а неполная — [Фамилия]. Соответственно, ссылка на элемент управления формы (главной формы, если она содержит подчиненную форму) имеет такую полную форму записи: Forms![<имя формы>]![<имя элемента управления>].

Здесь имени формы предшествует имя семейства открытых форм Forms, которому принадлежит данная форма. Эта «добавка» вызвана тем обстоятельством, что БД вполне может содержать таблицу и форму с одинаковыми именами, имеющих к тому же одноименные поля. В общем случае синтаксис оператора «!» таков: <класс объекта>!<имя объекта>

Оператор "." Оператор "." (точка) обычно используется для ссылок на свойства форм, отчетов и элементов управления. В частности, идентификатор поля со списком в форме выглядит так: Forms![<имя формы>]![<имя поля со списком>].[Text]. Здесь точка "." используется для отделения имени поля со списком от его свойства Text, которое возвращает текущее значение поля. В общем случае синтаксис оператора "." таков: <класс объекта>!<имя объекта>.<свойство объекта>

Подробную информацию о синтаксисе идентификаторов различныхобъектов можно найти в соответствующих разделах справки Access. Разработчики Access рекомендуют всегда ссылаться на объект или свойство, используя полную форму идентификатора. Если при создании выражения пользоваться построителем выражений, то эта рекомендация обычно автоматически выполняется.

4.2. Константы

В выражениях встречаются константы следующих типов: • Числовые константы (числа) — последовательность цифр, содержащая, если нужно, знак числа и разделитель целой и дробной части числа. В качестве разделителя в зависимости от установок Windows обычно используются "," (запятая) или "." (десятичная точка).Числа могут содержать символ Е или е и знак порядка, например,1,2Е+04 = 12 000. • Текстовые константы (строки) — могут содержать любые символы из набора символов кодовой таблицы ANSI. В выражениях строки нужно с обеих сторон заключать в прямые кавычки ("). Обычно Accessдобавляет их сам. • Константы типа Дата/время. Они должны быть заключены в знаки номера "#". Обычно Access добавляет их сам, если опознает, что вводится дата в одном из стандартных форматов "дд.мм.гг" или"дд/мм/гг".

4.3. Функции

В состав выражений часто входят различные функции. Всего в Accessи VBA определено более 160 функций. Здесь приводится краткое описание лишь части из них.

4.3.1. Функции для работы с датами • Date() — текущая дата. Может использоваться в формах и отчетах, атакже задавать условие отбора для запроса. Например, Date() – 1задает дату, предшествующую текущей дате. • Day(дата 1) — день месяца, целое число от 1 до 31. Например,Day(#10.08.99#) возвращает номер дня, равный 10. • DatePart(интервал 2; дата) — возвращает указанный в аргументе<интервал> компонент даты, целое число. Например, Datepart("q";#15.09.98#) возвращает число 3 — номер третьего квартала. • DateAdd(интервал; число; дата) — возвращает значение, содержащее дату, вычисляемую по формуле <дата> + <число> * <интервал>.Аргумент <интервал> принимает такие же значения, как и в функцииDatePart. Например, DateAdd("m";2;[Дата1]) возвращает дату, отстоящую от значения даты в поле Дата1 на два месяца.

• Format(дата, формат 3) — дает дополнительные возможности для использования в выражениях дат и возвращает строку символов. Строка <формат> может объединять несколько базовых форматов и символьных строк, которые заключаются в двойные кавычки. Например, Format( #22.03.73#; "dddd "","" d mmmm yy ""года""")возвращает строку четверг, 22 марта 73 года. • Month(дата) — месяц, целое число от 1 до 12. Например, Мonth(#10.08.99#) возвращает номер месяца, равный 8 (август). • Now() — дата и время компьютера. Часто используется в отчетах, созданных с помощью мастеров Access. • Weekday(дата) — день недели, целое число от 1 до 7, воскресенье равно 1. Например, Weekday(#22.03.73#) возвращает число 5 (четверг). • Year(дата) — год, целое число. Например, Year([Студенты]![Дата рождения]) возвращает год рождения студента.  

4.3.2. Функции для работы со строками • Chr(код_символа) — возвращает символ, соответствующий указанному коду символа из кодовой таблицы Windows ANSI. Например,Chr(100)="d", Chr(200)="И". • Left(строка, число_символов) — указанное число первых символов строки. Например, Left([Студенты]![Имя],1) возвращает первуюбукву имени студента. • Len(строка) — число символов в строке. Например, Len([Фамилия]) дает число символов в фамилии, содержащейся в поле Фамилия.

• Mid(строка; нач_символ; число_символов) — возвращает подстроку, содержащую указанное число символов строки, начиная с указанного символа. Последний аргумент необязателен. Если он отсутствует, то возвращаются все символы начиная с указанного символа до конца строки. Например, Mid("Студент Петров";9;4) возвращает «Петр», а Mid("Студент Петров";9) возвращает «Петров». • Right(строка, число_символов) — указанное число последних символов строки. Например, Right([Код студента],3) возвращает последние три символа кода студента. • Trim(строка) — удаляет пробелы в начале и конце строки символов.

4.3.3. Математические функции • Abs(выражение) — возвращает абсолютное значение числового аргумента выражение. Например, Abs(-10) = 10. • Int(выражение) — возвращает целую часть числового аргумента<выражение>. Например, Int(5,2) = 5, а Int(-5,2) = -6. • Rnd() — возвращает случайное число между 0 и 1.

4.3.4. Статистические функции Возвращают в качестве значения результат соответствующей статистической операции над данными, содержащимися в указанном поле запроса, формы или отчета. Записи с пустыми (Null) значениями в поле в вычислениях не участвуют. Обычно используются в итоговых запросах, при создании вычисляемых полей и инструкциях SQL. Avg(выражение1) — вычисляет среднее арифметическое значений, содержащихся в указанном поле. Например, Avg([Стипендия]![Сентябрь]) находит среднюю стипендию в сентябре. • Count(выражение) — определяет число записей, возвращаемых запросом. Например, Count([Стипендия]![Октябрь]) вычисляет число студентов, получивших стипендию в октябре.

• First(выражение) — возвращает значение, содержащееся в указанном поле первой записи результата запроса. Обычно результат запроса предварительно подвергается сортировке. Например, First([Студенты]![Фамилия]) находит фамилию самого молодого студента, если записи в запросе отсортированы по убыванию в поле [Датарождения]. • Last(выражение) — возвращает значение, содержащееся в указанном поле последней записи результата запроса. Например, Last([Студенты]![Фамилия]) находит фамилию самого молодого студента, если записи в запросе отсортированы по возрастанию в поле Датарождения.

• Max(выражение) — подсчитывает максимальное из значений, содержащихся в указанном поле. Например, Max([Студенты]![Дата рождения]) находит дату рождения самого молодого студента. • Min(выражение) — подсчитывает минимальное из набора значений, содержащихся в указанном поле. Например, Min([Книги]![Цена]) находит минимальную из цен на книги. • StDev(выражение) — возвращает значение несмещенной оценки стандартного отклонения значений, содержащихся в указанном поле. • Sum(выражение) — подсчитывает сумму значений, содержащихся в указанном поле. Например, Sum([Заказы]![Количество]*[Книги]![Цена]) вычисляет суммарную стоимость заказов. • Var(выражение) — возвращает значение несмещенной оценки дисперсии значений, содержащихся в указанном поле.  

4.3.5. Статистические функции по подмножеству Также позволяют вычислить статистические характеристики данных, содержащихся в указанном поле набора записей (таблицы или запроса).Однако синтаксис описываемых ниже функций дает возможность отобрать из этого набора с помощью логического условия подмножество записей и тем самым сузить область действия статистической операции. Другое важное отличие от функций, рассмотренных в предыдущем пункте, состоит в том, что статистические функции по подмножеству можно использовать для отбора нужных записей в выражении, содержащемся в строке условий запроса, а также в макросах и модулях

• DAvg(выражение; набор[; условие]1) — подсчитывает среднее арифметическое значений, содержащихся в указанном поле заданного подмножества записей. Например, DAvg(“[Ноябрь]”; ”Стипендия”)вычисляет среднюю стипендию в ноябре. • DCount(выражение; набор[;[условие]) — определяет число записей в указанном подмножестве записей. Так, DCount("[Код студента]";"Студенты"; "[Группа] = '9701’ ") находит количество студентов в учебной группе 9701.

• DLookUp(выражение; набор[; условие]) — возвращает значение указанного поля в заданном подмножестве записей. Например,DLookUp("[Оценка]"; "Сессия";"[Код студента] = '881375' and [Код экзамена] = '1' ") возвращает оценку студента Иванова по информатике. Обычно задаются условия, обеспечивающие уникальность значения, возвращаемого функцией DLookup. Если условиям отбора удовлетворяет несколько записей, то DLookup возвращает значение заданного поля первой из них. Если же ни одна из записей набора неудовлетворяет аргументу <условие>, то функция DLookup возвращает значение Null.

• DSum(выражение; набор[; условие]) — подсчитывает сумму значений, содержащихся в указанном поле заданного подмножества записей. Так, DSum("[Количество]"; "Заказы"; "[Код книги]=" & [введитекод]) находит число заказанных экземпляров данной книги. • DMax(выражение; набор[; условие]) — подсчитывает максимальное из набора значений, содержащихся в указанном поле заданного подмножества записей. Например, DMax(“[Цена]”;“Книги”; ”[Серия]=‘В подлиннике’”), возвращает максимальную из цен на книги серии“В подлиннике”.

• DMin(выражение; набор[; условие]) — подсчитывает минимальное из набора значений, содержащихся в указанном поле заданного подмножества записей. • DFirst(выражение; набор[; условие]) — возвращает значение, содержащееся в указанном поле первой записи заданного подмножества записей.

• DLast(выражение; набор[; условие]) — возвращает значение, содержащееся в указанном поле последней записи заданного подмножества записей. • DStDev(выражение; набор[; условие]) — возвращает значение несмещенной оценки стандартного отклонения значений, содержащихся в указанном поле заданного подмножества записей. • DVar(выражение; набор[; условие]) — возвращает значение несмещенной оценки дисперсии значений, содержащихся в указанном поле заданного подмножества записей.

4.3.6. Другие полезные функции • IsNull(выражение) — возвращает True, если <выражение> имеет значение Null; в противном случае функция возвращает значениеFalse. Например, значение IsNull([Стипендия]![Сентябрь]) равноTrue, если текущее значение поля пусто (данный студент не получает стипендию), и False в противном случае. • IIF(условие; выражение1; выражение2) — возвращает значение<выражение1>, если условие равно True и <выражение2>, если условие равно False. Например, IIF([Пол]="м"; "студент"; "студентка") имеет значение «студент», если в поле Пол содержится буква"м", и значение «студентка» — в противном случае.

• Nz(выражение[; представление]) — возвращает 0 (нуль), пустую строку ("") или другое указанное в аргументе <представление>значение, если <выражение> имеет значение Null. Например,Nz(([Стипендия]! [Сентябрь];"нет") возвращает значение стипендии студента за сентябрь, если он в сентябре получал стипендию, или слово«нет» в противном случае. Аргумент <представление> необязателен. Если он отсутствует, то функция Nz возвращает нуль или пустую строку в зависимости от контекста, требующего числовое или текстовое значение.

4.4. Построитель выражений

При создании выражений для таблиц, запросов и других объектовAccess следует использовать построитель выражений. Для вызова построителя нужно сначала щелкнуть по ячейке, в которую будет вводиться выражение, а затем по кнопке Построить на панели инструментов или по кнопке , обычно появляющейся справа от ячейки ввода. На экране появится окно Построитель выражений (см. рис. 4.1), содержащее четыре поля.

В верхнем поле располагается создаваемое выражение, а три нижних используются для выбора нужных элементов. Для выбора любого элемента в этих полях нужно сделать двойной щелчок по соответствующему имени. Левое поле отображает иерархию папок, содержащих основные типы компонентов выражений. После выбора элемента (папки) из левого поля в среднем поле будет выведен либо список его элементов (поля таблицы или запроса), либо список подтипов (элементы управления формы, категории функций и т.п.). При выборе подтипа в правом поле появится список его элементов (поля, функции, свойства элементов управления).Чтобы ввести в формируемое выражение ссылку на имя поля таблицы или запроса, нужно выбрать в левом поле таблицу или запрос, а затем в среднем поле нужное поле.   В верхнем поле располагается создаваемое выражение, а три нижних используются для выбора нужных элементов. Для выбора любого элемента в этих полях нужно сделать двойной щелчок по соответствующему имени. Левое поле отображает иерархию папок, содержащих основные типы компонентов выражений. После выбора элемента (папки) из левого поля в среднем поле будет выведен либо список его элементов (поля таблицы или запроса), либо список подтипов (элементы управления формы, категории функций и т.п.). При выборе подтипа в правом поле появится список его элементов (поля, функции, свойства элементов управления).Чтобы ввести в формируемое выражение ссылку на имя поля таблицы или запроса, нужно выбрать в левом поле таблицу или запрос, а затем в среднем поле нужное поле.  

Для ввода функции следует выбрать в левом поле папку Функции, а затем Встроенные функции. В среднем поле нужно выбрать категорию или вариант <Все>, а затем, прокрутив список в правом поле, — нужную функцию. Для ввода оператора ( +, >, And и др.) щелкните по соответствующей кнопке в окне построителя. Если требуемого оператора на кнопках нет, следует открыть в левом поле папку Операторы. Затем в среднем поле выбрать категорию или вариант <Все>, а в правом поле — нужный оператор.

Access часто вставляет в создаваемое выражение вместе с выбранным элементом один или несколько прототипов, заключенных в кавычки(«выражение», «number» и т.п). В этом случае нужно либо ввести вместо прототипа соответствующее значение, либо выделить прототип и заменить его элементом из правого списка, либо просто удалить его.

Для вставки элемента в выражение можно использовать кнопку Вставить. Чтобы отменить ошибочный ввод, нужно щелкнуть по кнопке Назад. Создание выражения завершается нажатием кнопки ОК.