Порядок выполнения операций
Если о вашем выражении длинная строка вычислений, программа Access следует строгим правилам определения старшинства операций или, говоря математическим языком, учитывает,
какое вычисление выполняется первым при наличии нескольких вычислений в выражении. Итак, если у вас длинное выражение, Access не просто обрабатывает его слева направо. Вместо этого программа оценивает выражение фрагмент за фрагментом в следующем порядке:
1. Скобки (любые вычисления, заключенные в скобки, Access всегда выполняет первыми).
2. Проценты.
3. Возведение в степень.
4. Деление и умножение.
5. Сложение и вычитание.
Предположим, что вы хотите использовать поля QuantityInStock(количество на складе) и QuantityOnOrder(количество в заказах) для подсчета всех товаров, имеющихся в наличии и находящихся на пути к клиенту. Если вы не знаете правил старшинства операций, то можете сформировать следующее выражение:
TotalValue: [UnitsInStock] + [UnitsOnOrder] * [Price]
Проблема состоит в том, что программа Access перемножит поля QuantityOnOrderи Priceи добавит полученное значение к значению ноля QuantityInStock.Для устранения этой оплошности нужно применить скобки следующим образом:
TotalValue: ([UnitsInStock] + [UnitsOnOrder]) * [Price]
Теперь поля QuantitylnStockи QuantityOnOrdeгсуммируются, а затем умножаются на поле Priceдля получения общей суммы.
Подсказка
Вам нужно больше свободного места для набора длинного выражения? Можно расширить любой столбец в Конструкторезапросов для увеличения видимой зоны, но в случае сложных вычислений этого все равно не хватит. Лучше щелкнуть кнопкой мыши в области Полеи нажать сочетание клавиш <Shift>+<F2>. Это действие распахивает на экране диалоговое окно Область ввода(Zoom), отображающее все содержимое в большом текстовом поле, разделенном на столько строк, сколько вам нужно. Когда просмотр или редактирование выражения завершены, щелкните мышью кнопку ОК,чтобы закрыть окно и сохранить изменения, или кнопку Отменадля отказа от них.
Выражения с текстовыми значениями
Несмотря на то, что обычно вычисляемые поля имеют дело с числовыми данными, но так бывает не всегда, существуют действительно удобные способы обработки текста.
Если у вас есть текстовые данные, конечно нельзя применять сложение, вычитание и другие арифметические операции. Но можно слить текст. Есть возможность связать несколько полей с адресной информацией и отображать их все в одном поле, экономя пространство (и, возможно, облегчая экспорт этих данных и другую программу).
Для слияния текста применяется оператор амперсанд (&). Далее показано, как создать поле FullName(ФИО), в котором собрана информация из полей FirstNameи LastName:
FullName: [FirstName] & [LastName]
Это выражение выглядит достаточно корректным, но на самом деле у него есть один недостаток. Поскольку вы не вставили никаких пробелов, имя и фамилия в результате прижаты
друг к другу, например, так: BenJenks. Лучше слить три фрагмента текста: имя, пробел и фамилию. Вот исправленная версия: FullName: [FirstName] & " " & [LastName]
Приведенное выражение создаст значение: Ben Jenks. Можно также поменять местами имя и фамилию и отделить их запятой, если вы предпочитаете указывать фамилию первой (например, Jenks, Ben) для облегчения сортировки: FullName: [LastName] & ", " & [FirstName]
Примечание
В программе Access есть два типа текстовых значений: те, которые вы извлекаете из других полей, и те, которые вы вводите непосредственно (или фиксированные). Когда вы вводите текстовые константы, такие как запятая или пробел в предыдущем примере, их следует заключать в кавычки, чтобы программа Access знала, где начинается и заканчивается текст.
Вы даже можете применять амперсанд для сцепления текста с числовыми значениями. Если вы хотите, чтобы слегка бесполезный текст "The price is" появлялся перед каждым значением цены, примените следующее вычисляемое поле:
Price: "The price is: " & [Price]
Функции запросов
Теперь, возможно, вам приходит в голову мысль о том, что манипулировать числами и текстом можно более искусными способами — способами, которые предоставляют гораздо больше возможностей, чем простые арифметические операции. Вам может потребоваться округление чисел или преобразование строчных букв в прописные. Программа Access действительно предоставляет средства, называемые функциями и позволяющие поднять ваши выражения на более высокий уровень.
Функция — это реализация встроенного алгоритма, принимающая ваши данные, выполняющая вычисления и возвращающая результат. Разница между функциями и математическими операциями, как вы уже убедились на деле, заключается в том, что функции могут выполнять более сложные действия. У программы Access есть каталог с десятками разных функций, многие из которых реализуют такие приемы, которые вы даже не надеялись выполнить самостоятельно.
Функции пригодятся для всех видов обработки данных в программе Access. Вы можете примять их в:
■ вычисляемых полях для включения данных в результаты ваших запросов;
■ условиях отбора для задания записей, отображаемых в запросе;
■ коде на языке Visual Basic, многоцелевой расширяемой системе для программы Access, с которой вы познакомитесь в части V.
Изучив как следует мир функций, вы поймете, что многие из них хорошо подходят для вычисляемых полей, но не для условий отбора. В следующих разделах вы увидите, где именно разумнее всего использовать каждую из них.
Примечание
Функции — это встроенная часть версии языка SQL (см. разд. "Режим SQL" главы 6), применяемой в программе Access для обработки данных.
Применение функций
Применяете ли вы простейшую или сложнейшую функцию, синтаксис — правила применения функции в выражении — остается неизменным. Для использования функции просто введите ее имя с последующими скобками. Затем внутрь скобок поместите все данные, которые нужны функции для выполнения ее вычислений (если это необходимо).
Для примера рассмотрим очень полезную функцию Round (), которая принимает число с дробной частью и убирает любые нежелательные позиции в дробной части. Эта функция — удобный способ приведения в порядок отображаемых значений вычисляемого поля. Вы оцените полезность функции Round (), если создадите выражение, подобное приведенному далее и вычисляющее цены, сниженные на 5%:
SalePrice: [Price] * 0.95
Примените это выражение к цене $43.97 и получите в результате 41.7715, что вряд ли будет выглядеть уместно на ценнике товара. Здесь пригодится функция Round (). Передайте ей неокругленное число и число знаков в дробной части, которое нужно сохранить:
SalePrice: Round([Price] * 0.95, 2)
Технически функции Round ( ) требуется две порции информации или два аргумента. Первый — это число, которое округляется (вданном случае это результат вычисления Price * 0. 95), а второй — количество цифр, которое вы хотите оставить справа от десятичной точки (2). В результате получается значение, округленное до двух десятичных знаков, или 41.77.
Примечание
Большинству функций, подобных Round (), требуются два или три аргумента. Но некоторые функции могут принять гораздо больше, в то время, как нескольким функциям вообще не нужны аргументы
Часто задаваемый вопрос
Банковское округление
Похоже, программа Access округляет числа неправильно. Как быть?
Вас может удивить то, что Access округляет число 21.985 до 21.98. Если вас учили округлять до большего числа, числа заканчивающиеся цифрой 5, то вы считаете, что результат должен быть 21.99. Этот способ называют арифметическим округлением. Программа Access не применяет арифметическое округление — она выбирает банковское округление, которое лучше в некоторых случаях.
Разница между арифметическим и банковским округлением заключается в трактовке цифры 5. Поскольку число 21.985 находится точно в середине, между числами 21.98 и 21.99, не просто решить, что с ним делать. При постоянном округлении числа с 5 на конце до большего числа вносится систематическое отклонение в итоги и средние значения. Поскольку вы округляете до большего чаще, чем до меньшего, любые итоги или среднее, которые вы вычисляете, получаются чуть больше, чем следовало бы.
Банковское округление решает эту проблему округлением 5 в одних случаях до большего числа, а в других до меньшего, в зависимости от соседней четной или нечетной цифры.
Число 21.985 округляется до меньшего числа 21.98, а число 21.995 — до большего, 22. Это не единственный способ борьбы с систематическим отклонением (можно решать случайным образом, когда округлять, а когда нет), но это общепринятая практика в бухгалтерских расчетах и статистике.
Вложенные функции
В вычисляемом поле или условии отбора можно применять несколько функций. Этот прием известен как вложенные функции: специальный термин, обозначающий вставку одной функции внутрь другой. Например, в программе Access есть встроенная функция определения абсолютного значения числа Abs ( ), преобразующая отрицательные числа в положительные (и оставляющая положительные числа без изменения). Далее приведен пример деления одного поля на другое и получения в результате заведомо положительного значения:
Speed: Abs ([DistanceTravelled] / [TimeTaken])
Если полученный результат нужно округлить, можно поместить целиком все выражение внутрь скобок функции Round ( ):
Speed: Round (Abs([DistanceTravelled] / [TimeTaken]), 2)
Вычисляя выражение с вложенными функциями, программа Access сначала вычисляет результат самой внутренней функции. В данном примере, прежде всего, определяется абсолютное значение, а затем результат округляется. В приведенном далее примере порядок вычислений изменен на обратный без изменения результата:
Speed: Abs(Round([DistanceTravelled] / [TimeTaken], 2) )
Во многих случаях порядок вложения функций имеет значение, и разный порядок приводит к различным результатам.
Вложенные функции быстро превращают выражение в потенциально опасное. Даже в сравнительно простом примере вычисления скорости трудно предсказать результат без пошагового вычисления выражения. Одна не на месте стоящая или пропущенная скобка может привести к сбою вычисления. Если вы применяете вложенные функции, не пишите сразу выражение целиком — включайте функции в выражение поочередно и после вставки очередной функции выполняйте вычисление.
Построитель выражений
Функции — замечательное нововведение, но в программе Access может оказаться слишком много замечательных вещей. Программа предлагает перечень из десятков различных функций, связанных с решением разнообразных задач, некоторые из этих функций предназначены для выполнения специализированных математических и статистических вычислений.
Примечание
В этой книге описана далеко не каждая функция {если бы это было так, вам пришлось бы, сидя над книгой, бороться со сном). Но в следующих разделах вы познакомитесь с наиболее полезными функциями для работы с числами, тестом и датами. Для поиска дополнительных функций используйте Построитель выражений(Expression Builder) Если же вы предпочитаете интерактивный режим обучения, проверьте информативный Web-pecypc www.techonthenet.com/access/functions.
Для быстрого поиска нужных вам функций Access предлагает компонент, именуемый Построителем выражений.Для его запуска выполните следующие действия:
1. Откройте запрос в Конструкторе.
2. Щелкните правой кнопкой мыши поле, в которое вы хотите вставить выражение, и выберите команду Построить(Build).
Если вы создаете вычисляемое поле, нужно щелкнуть правой кнопкой мыши в ячейке Поле,Если создается условие отбора, следует щелкнуть правой кнопкой мыши в ячейке Условие отбора.
После выбора команды Построитьна экране появляется окно Построителя выражений,отображающее текущее содержимое поля (рис. 7.3).
Рис. 7.3. Построитель выраженийсостоит из текстового поля в верхней части окна, в котором можно редактировать выражение, кнопок быстрой вставки знаков обычных операций (таких как +, -, / и *, если почему-либо вы не можете их найти на клавиатуре), и трехпанельного обозревателя в нижней части окна, который поможет найти нужные поля и функции
3. Вставьте или отредактируйте выражение.
У Построителя выраженийесть два ускоряющих приема работы, которые вы, может быть, захотите испробовать. Можно вставлять имя без ввода с клавиатуры (рис. 7.4) и можно найти функцию с помощью обзора (рис. 7.5).
Примечание
Построитель выражений— универсальное средство создания выражений в вычисляемых полях и условиях отбора. Некоторые параметры имеют смысл только в одном из его назначений. Логические операторы, такие как символ равенства (=), And, Or, Not и Like, удобны для задания условий фильтрации (см. разд. "Построение условий отбора" главы 6), но бесполезны в вычисляемых полях.
 |
 |
Рис. 7.4. Для вставки имени поля щелкните дважды кнопкой мыши папку Таблицыв самом левом списке. Затем щелкните мышью вложенную папку, соответствующую нужной вам таблице. И, наконец, дважды щелкните кнопкой мыши имя поля в среднем списке для добавления его в ваше выражение. Этот прием рекомендуется только тем, кто любит щелкать кнопкой мыши
Рис. 7.5. Для поиска функции начните с двойного щелчка кнопкой мыши папки Функциив левом списке. Затем выберите вложенную папку Встроенные функции.(Другие варианты отображают любые пользовательские функции, которые вы добавили в вашу БД с помощью пользовательского кода VBA.) Далее выберите категорию функции в среднем списке. В правом списке показаны все функции в выбранной категории. Вы можете дважды щелкнуть кнопкой мыши функцию для вставки ее в выражение
Примечание
Когда вы вставляете имена полей в Построителе выражений,они записываются в более длинном формате с обязательным указанием имени таблицы. Вы увидите [Products] ! [Price] вместо просто [Price]. He волнуйтесь — для программы Access это одно и то же.
4. Щелкните мышью кнопку ОК.
Программа Access скопирует ваше новое выражение в ячейку Поле или Условие отбора.
Примечание
 |
Когда в Построителе выраженийвставляется функция, программа добавляет заполнители (например, <number> и <precision>), на место которых нужно ввести аргументы. Замените этот текст нужными вам значениями.
Рис.7.6. В справке функции Round () описываются ее назначение и два параметра. Второй параметр — количество знаков в дробной части — заключен в квадратные скобки, что означает необязательное значение. Пропустите его, и программа Access округлит значение до ближайшего целого числа. Слева приведен перечень функций, который позволяет просмотреть любую другую функцию Access и прочесть ее описание
Большинство специалистов Access считают Построитель выраженийслишком тяжеловесным, чтобы использовать его в работе. Но несмотря на то, что это не самое эффективное средство написания выражений, построитель предоставляет прекрасный способ знакомства с новыми загадочными функциями, благодаря своей справке для встроенных функций. Если вы нашли функцию, которая кажется многообещающей, но хотите получить дополнительную информацию, выберите ее в списке и щелкните мышью кнопку Справка(Help). Вы будете вознаграждены кратким резюме, объясняющим назначение функции и описывающим ее параметры, которые нужно задать, как показано на рис. 7.6.
Форматирование чисел
Format ( ) — интересная математическая функция, преобразующая числа в текст. Она интересна, потому что создаваемый текст можно отформатировать несколькими способами, управляя, таким образом, представлением чисел.
Для того чтобы понять разницу, вернемся к выражению, использованному ранее для снижения цены:
SalePrice: [Price] * 0.95
Даже если у поля Priceне Денежныйтип, вычисленные значения в поле SalePrice(продажная цена) выводятся как обычные числа (без знака валюты, разделителя тысяч и т. д.). Вы увидите значение 43.2 вместо желаемого $43.20.
Решить эту проблему можно, применив функцию Format ( ) для задания денежного формата вывода:
SalePrice: Format([Price] / 0.95, "Currency")
Теперь вычисленные значения содержат знак валюты. Более того, поскольку денежные суммы отображаются с двумя знаками после точки, вам не нужно больше применять функцию Round ( ).
Хитрость применения функции Format () состоит в выборе текста, задаваемого в качестве второго аргумента для получения желаемого результата. В табл. 7.2. приведены возможные варианты.
Таблица 7.2. Варианты форматирования
| Формат | Описание | Пример |
| Денежный | Выводит число с двумя знаками в дробной части, разделителями для тысяч и знаком валюты | $1 433.20 |
| Фиксированный | Отображает число с двумя десятичными знаками | 1433.20 |
| Основной | Выводит на экран число с двумя десятичными знаками и разделителями тысяч | 1 433.20 |
| Процентный | Отображает процентное значение (число, умноженное на 100, и со знаком процента). Выводит две цифры справа от десятичной точки | 143320.00% |
| Экспоненциальный | Отображает число в научной нотации с двумя десятичными знаками | 1.43Е+03 |
Таблица 7.2 (окончание)